Using Excel I am using a VLOOKUP where the lookup_value contains a formula that generates the string that should be searched. The formula:
=VLOOKUP(MID(A2,2,5),Sheet2!A:B,2,0)
All results come back with #N/A, yet if i substitute the MID() function with the value it generates the VLOOKUP functions fine.
Example data:
Sheet1
$#00001#
$#00002#
Sheet 2
00001 | Hello
00002 | World
The 00001 in sheet2 is actually a 1 in a cell that has been formatted as "00000" to display leading zeroes. This does not alter the raw underlying value of 1. You will need to convert the MID result to a true number with a double unary like,
=VLOOKUP(--MID(A2,2,5),Sheet2!A:B,2,0)
You cannot lookup text-that-looks-like-a-number in a column of true numbers regardless of how the true numbers are formatted for display.
Tip: Unless you manually change the cell alignment, text is left-aligned by default; numbers are right-aligned by default. This is a quick visual check that can prove the above scenario.
i agree with Jeeped's answer. however, looks like if $ was a literal, formula should actually contain
MID(A2,3,5)
as 00001 starts at position 3.
Formatting the column A in Sheet2 as Text is also a solution.
P.S. i am writing as answer since i was unable to comment on the question instead.
