## 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:

Two-way lookup using multiple tables [UDF]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

Two-way lookup using multiple tables [UDF]

**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:

### Download excel *.xlsx file

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Gets a value in a specific cell range based on a row and column number.

The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula […]

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]

Get date ranges from a schedule

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

### 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.

**Contact Oscar**

You can contact me through this contact form