Using VLOOKUP when the value in table array is another function

[bbrunof]

vlookup is returning #N/A because the value I am searching for in table_array is a function. I.E. I am searching for an exact match of the value 125 in table_array, but that value in table_array in the result of the function =LEFT(B72,3).

The function I am using now is: =VLOOKUP(A20, 'Self-Assessment Summary'!A:E, 3, FALSE)

[NBVC]

Try:

=VLOOKUP(""&A20, 'Self-Assessment Summary'!A:E, 3, FALSE)

or change the LEFT formula to: =LEFT(B72,3)+0 to coerce to a number...

[bbrunof]

don't get why ""& does the trick, but it did. Thanks!

[NBVC]

Because the value in A20 is numeric, and the LEFT() function returns text string (even though it looks like a number)... the "" coerces the A20 value to text so that it can make the match.