Match Function Problem with Numbers

Question

I'm using Excel's Match function on an array of card names such as AK, Q9, 77 , 54. To search for a value associated with a card I use the Left and Right functions, such as Left(Cell X): Example:

Match(Left(G5), A1:A13,0), where G5 may hold AJ or Q8 or 76 etc..

 If the cell has only letters, such as QJ, all good. But if one or both cards is a number, such as 86, I get #N/A. I tried Paste Value of the Left or

Right function for the search value but that didn’t work.

Any suggestions?

Answer 1

Hi,

Does this work?

=IFERROR(Match(Left(G5)*1, A1:A13,0),Match(Left(G5), A1:A13,0))

Answer 2

Didn't work and actually made an ok search invalid. Any other ideas? I would think others must have faced this problem.

Thanks for your reply

Answer 3

How about

=MATCH(LEFT(G5), A1:A13&"", 0)