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.
Get Excel *.xlsx file
Choose between two data sets to VLOOKUP.xlsx
Vlookup category
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
Excel categories
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.