Question: How do I search a specific data set, I have two tables to choose from?

Answer:

Formula in cell C13:

Define named ranges

You also need to define the data sets as named ranges.

1. Select cell range B4:C9
2. Type in the name box the name you want to use. I wrote Table1.

Repeat above steps with the second data set, I named it Table2. You may define more named ranges if you like.

Dataset layout

Make sure that the tables have data arranged the same way, the VLOOKUP function gets the value from column 2 in the example above.

If you don't have the columns arranged in the same way in both tables you will get the wrong value.

Explaining formula

Step 1 - Convert named range to a cell reference

The VLOOKUP function can't evaluate a text string in the formula, however, the INDIRECT function lets you do that.

INDIRECT(C11)

becomes

INDIRECT("Table1")

and returns B4:C9.

Keep in mind that the INDIRECT fiunction is volatile and may slow down your workbook considerably if used extensively.

Step 2 - Get the correct value from a given column number based on a lookup value

VLOOKUP(C12,INDIRECT(C11),2,0)

becomes

VLOOKUP(C12,B4:C9,2,0)

becomes

VLOOKUP("bus",B4:C9,2,0)

and returns 2 in cell C13.

Get Excel *.xlsx file

Choose between two data sets to VLOOKUP.xlsx

Vlookup category

How to use VLOOKUP/XLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

VLOOKUP/XLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]

Use VLOOKUP to calculate discounts, commissions, tariffs, charges, shipping costs, packaging expenses or bonuses
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]

Excel categories

Leave a Comment