help with vlookup

[rcirone]

I am using 3 formula pulling from the same cell (A2) which that is a formula in the cell. the vlookup and the index will not see the number in cell A2 that I get from a formula (right(A2search("",A2,10)). if I type the number in it will work. can you help me.

=VLOOKUP(A2,Sheet5!A1:Z180,9,FALSE)
=INDEX(Sheet5!$I$2:$I$185,MATCH(Sheet2!A2,Sheet5!$P$2:$P$185,))

 

[Weazel]

maybe...

=VLOOKUP(A2+0,Sheet5!A1:Z180,9,FALSE)
=INDEX(Sheet5!$I$2:$I$185,MATCH(Sheet2!A2+0,Sheet5!$P$2:$P$185,))

 

[rcirone]

the top one work on 90% of them the other one it gives me #N/A

 

[Aladin Akyurek]

Augmenting the suggestion, we would get:

=IFERROR(VLOOKUP(A2+0,Sheet5!A1:Z180,9,FALSE),IFERROR(VLOOKUP(A2&"",Sheet5!A1:Z180,9,FALSE),"not found")

 

[jtakw]

Hi,

That's because your formula to extract the Number returns Text, change That formula to:

=right(A2,search("",A2,10))+0

Then your VLOOKUP or INDEX/MATCH should work fine.

 

[rcirone]

Thanks that worked

 

[jtakw]

You're welcome.