Two-dimensional lookup using two tables
The following formula performs a two-way lookup in two different tables.
Formula in cell D20:
If the value is not found in table 1 the formula continues to table 2. If nothing is found in table 2 as well the formula returns #N/A.
The formula does not return multiple values from different tables, in that case, check out this User defined Function:
Recommended articles
This article describes two ways to perform lookups in multiple in multiple cross reference tables simultaneously. The first one being […]
Formula in cell D21:
The formula above checks if the value is found in table 1 or table 2.
Explaining formula in cell D20
Step 1 - Find relative position of y value in table 1
The MATCH function looks for number 8 in the vertical cell range B4:B8.
MATCH(C19,B4:B8,0)
becomes
MATCH(8,{1;3;5;7;9},0)
and returns #N/A. The value is not found.
Step 2 - Find relative position of x value in table 1
The second MATCH function looks for "F" in the horizontal cell range C3:G3.
MATCH(C18,C3:G3,0)
becomes
MATCH("F",{"A","C","E","G","I"},0)
and returns #N/A. The value is not found.
Step 3 - Get value based on coordinates in table 1
INDEX(C4:G8,MATCH(C19,B4:B8,0),MATCH(C18,C3:G3,0))
becomes
INDEX(C4:G8,#N/A,#N/A)
and returns #N/A. The value is not found in the first table.
Step 4 - IF error continue to table 2 and repeat
=IFERROR(INDEX(C4:G8, MATCH(C19, B4:B8, 0), MATCH(C18, C3:G3, 0)), INDEX(C12:G16, MATCH(C19, B12:B16, 0), MATCH(C18, C11:G11, 0)))
becomes
=IFERROR(#N/A,INDEX(C12:G16,MATCH(C19,B12:B16,0),MATCH(C18,C11:G11,0)))
becomes
=IFERROR(#N/A,INDEX(C12:G16,4,3))
becomes
=IFERROR(#N/A,656)
and returns 656 in cell D20.
Multiple tables
It is possible to use more than two tables, simply use this template:
The formula would then be:
Get excel *.xlsx file
Two-dimensional lookup using multiple tables.xlsx
Two dimensional lookup category
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
Excel categories
Leave a Reply
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.
