Example and Justification
Figure 1. A dynamic Lookup table with INDIRECT
To permit a dynamic lookup table, you can utilize the INDIRECT function containing the named ranges within VLOOKUP.
In figure 1 the formula shown in G7 is:
How this formula functions
Initially, this is a standard VLOOKUP formula. The INDIRECT to return a valid table column.
In the figure shown above, two named ranges have been made: “table1” alludes to B5:C7, and “table2” alludes to B10:C12*.
INDIRECT gets the content in E7 (“table1”) and settle it the named range table1, which takes steps to B10:C12, or, in other words, VLOOKUP.
VLOOKUP plays out the lookup and returns 10 for the shaded “shoe” in table1.
I could see this being helpful when the query lookup figures out which page the data searched is on