Using INDIRECT() to set dynamic range with VLOOKUP()

2
votes

I am trying to use INDIRECT() to set a range for a VLOOKUP() function based on a defined numeric value. When stepping through the VLOOKUP() formula, the INDIRECT() function returns what appears to be an acceptable range for my purposes. However, when running the formula, a "#N/A" error is returned and I'm not sure why.

To give a little more flavor, I'm using LEFT() and ADDRESS() to determine the column heading letter based on a MATCH() of a separate value across the top row. Using a fixed offset, the MATCH() functions return the correct column headers and I supply the constants for the range size. Below is my formula:

=VLOOKUP(J2,INDIRECT("$"&LEFT(ADDRESS(1,MATCH($H2,ZAS_Lookup,0)+8,4),1+(MATCH($H2,ZAS_Lookup,0)+8>26))&"$1:$"&LEFT(ADDRESS(1,MATCH($H2,ZAS_Lookup,0)+9,4),1+(MATCH($H2,ZAS_Lookup,0)+9>26))&"$29"),2,TRUE)

As I mentioned, when stepping through to the last step before final calculation, the formula appears as if it will condense down to the following:

=VLOOKUP(5,$DW$1:$DX$29,2,TRUE)

This is not the case, however, since it returns a #N/A. Any idea why? Do I need to use a different function to set the range dynamically?

1
excelvlookupformulasexcel-indirect
what you get if use directly =VLOOKUP(5,$DW$1:$DX$29,2,TRUE)? – Dmitry Pavliv
"If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value" - from MS documentation. Is it true for you? office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx – Dmitry Pavliv
Using it directly worked, hence why I was so confused. However, I overlooked the fact that when you use INDIRECT, you need to include the tab name, I believe even if the table is on the same tab as the lookup. – Nate
if table is on the same sheet as the Vlookup, it's no need to use sheet name in INDIRECT function – Dmitry Pavliv
It's almost never a good idea to use ADDRESS and INDIRECT as you have, for example you are "hardcoding" so much in that formula that if you ever add or delete rows in the target sheet it will all fall apart. you can probably use INDEX much more simply and cleanly (and robustly) - if you are interested can you explain the logic of your approach - what's the actual range that's defined by ZAS_Lookup? is that on the same sheet as the table you want to derive? – barry houdini

1 Answers

1
votes

I realized that I needed to name the tab on which the table resides. It is not enough to simply set the ranges - I also needed to tell Excel which sheet the table was on.