Choose between two data sets to VLOOKUP
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.
- Select cell range B4:C9
- 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.
Download Excel *.xlsx file
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are [โฆ]
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD [โฆ]
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column [โฆ]
Prevent duplicate records [Data Validation]
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share [โฆ]
Highlight lookups in relational tables
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add [โฆ]
How to use VLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built [โฆ]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found [โฆ]
VLOOKUP 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? Answer: Array formula in [โฆ]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row [โฆ]
Return multiple values if in range
The image above shows a formula in cell C11 that extracts values from column D if the number in cell [โฆ]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish