excel vlookup lookup_range cell contains a formula

Question

I have a straightforward formula in Excel 2010 as follows:

=VLOOKUP(D597,'Sheet1'!$AA$3:$AC$5000,3,FALSE)

The problem is the lookup_range, D597 in this case, contains a formula, not text or numbers (it is actually a reference to another sheet and cell).

The lookup is returning #N/A . I know the value is in the named range, column aa, and the value I want in column 3.

Is there a way to use vlookup, or another function, to accomplish this fairly straightforward lookup?

What is the formula or reference you're using for D597? and the value that it D597 returns, is it of type date? time? text? number? — Namkce
And are you sure it's not suppose to be =VLOOKUP(D597,Sheet1!$AA$3:$AC$5000,3,FALSE)? — Namkce

bluewater bluewater · Accepted Answer · 2016-07-25T20:19:22

With help from a friend, Master Joe, I was able to pull the value underlying D597 in the above vlookup formula. The solution:

VLOOKUP(VALUE(TRIM(D597)),'Sheet1'!$AA$3:$AC$5000,3,FALSE)