VLOOKUP that refers to a number as a result from a formula showing NA

[NVRensburg]

Please could I get some help. I'm using a VLOOKUP formula that uses the LOOKUP-VALUE which is a number from a result from another formula in that cell. It gives a result as NA, but if I type the number "1" in there, then it works.

The VLOOKUP formula is =VLOOKUP(L1,Sheet1!A:B,2,FALSE)

L1 formula contents is =MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,255) which returns the value "1"

Please could someone assist as to how I can use the VLOOKUP formula that will work?

 

[steve the fish]

Thats because your number is text. A textual number is not seen as the same as a real number. You could try:

=IFERROR(VLOOKUP(L1+0,Sheet1!A:B,2,FALSE),VLOOKUP(L1,Sheet1!A:B,2,FALSE))

 

[NVRensburg]

Thank you so much!!! That works

Thats because your number is text. A textual number is not seen as the same as a real number. You could try:

=IFERROR(VLOOKUP(L1+0,Sheet1!A:B,2,FALSE),VLOOKUP(L1,Sheet1!A:B,2,FAT

 

[Peter_SSs]

which is a number from a result from another formula in that cell.

If it is always going to be a number, then it would be simple to do the "+0" in the L1 formula and keep your original simple VLOOKUP formula

=MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,255)+0

=VLOOKUP(L1,Sheet1!A:B,2,FALSE)