Match & Index

[Tekhnikos]

I have an Index and Match formula which is supposed to match a number
formatted as text. I keep getting #N/A in the match cell. If I choose the
"convert to number" selection in the error checking option, the formula
works. I have tried to change the property to numbers and still receive an
error. I have used this formula in the past without issue.

 

[Mike H]

post your formula

 

[Tekhnikos]

=INDEX(Selections!A2:E441,MATCH(Data!A2,Selections!A2:A441,0),4)
The format of SelectionsA2 is text, the format of Data!a2 is text

 

[Tekhnikos]

I also tried using VLookup function as well - I have the same error. The
cell that is supposed to be matched is based a combo box selection.

 

[Mike H]

Hi,

the formula works fine text or numbers in the lookup value or range so i
suspect your text values may not be what you think they are, erronious spaces
maybe

I'd use this to do the same thing

=VLOOKUP(TRIM(Data!A2),Selections!A2441,4,FALSE)

Note that in you formula column e is referenced but not used.

Mike

 

[Tekhnikos]

What appears to be happening is a hidden value of some sort is added to the
cell from the combo box selection. The LinkedCell from the combo is used for
the lookup.. any ideas on how to stop this from happening?

 

[Pete_UK]

You could use as your lookup value:

LEFT(Data!A2,LEN(Data!A2)-1)

if that extra character is at the end of Data!A2 (or use RIGHT if it
is at the beginning).

Hope this helps.

Pete