Excel VLOOKUP in Different Ranges
You can use the VLOOKUP function to find data in a lookup table, based on a specific value. If you enter a product number in an order form, you can use a VLOOKUP formula to find the matching product name or price.
In some Excel workbooks, you might need to pull data from a specific table, depending on an option that the user has selected.
For example, in the screen shot below, there are different rate tables for the East and West regions. Range B3:C6 is named Rates_East and range E3:F6 is named Rates_West.
Create the VLOOKUP Formula
On the data entry sheet, if East is entered in column A, then the VLOOKUP formula should use Rates_East as the lookup table. If West is entered as the region, the rate should come from the Rates_West table.
If there are only a couple of lookup tables, you could use an IF function to select the correct table in the VLOOKUP.
This solution could work in this example, where there are only two rate tables.
Select the Correct Lookup Table
For situations when there are multiple lookup tables, an IF function probably wouldn't be practical. Instead, you can use the INDIRECT function to return the correct lookup range.
=VLOOKUP(B3,INDIRECT("Rates_" & A3),2,0)
The INDIRECT function combines the text string "Rates_" with the region entered in column A, and returns the range with that name. This makes the VLOOKUP formula very flexible, and you could use any number of lookup tables in the workbook.