# How to perform a two-dimensional lookup

#### Table of Contents

## 1. 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) value and the column (mm) value and want to return the x/y value. i.e I have 25/X and 9/Y item and want 1.8 to be returned.

(mm) 22 23 24 25 26 27 28 29

8 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3

9 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3

10 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3

11 2.2 2.8 2.8 2.8 2.8 2.8 3.3 3.3thanks

You can't use VLOOKUP in this case, you need to do two different lookups to locate the value you want based on coordinates. A two-way lookup.

**Formula in D11:**

If the x or y value is not found the formula returns #N/A.

### Explaining formula in cell D11

*Step 1 - Find relative position of y value*

MATCH(D10, C4:C7, 0)

becomes

MATCH(9,{8;9;10;11},0)

and returns 2.

Number 9 is found at the second location in this array:Â {8;9;10;11}.

*Step 2 - Find relative position of x value*

MATCH(D9,D3:K3,0)

becomes

MATCH(25,{22,23,24,25,26,27,28,29},0)

and returns 4.

Number 25 is at the fourth position in the array.

*Step 3 - Get value based on coordinates*

=INDEX(D4:K7,2,MATCH(D9,D3:K3,0))

becomes

=INDEX(D4:K7,2,4)

becomes

=INDEX(D4:K7,2,4)

and returns 1.8 in cell D1.

The value is in the fourth column and the second row in cell range D4:K7. INDEX function retrieves that value based on row and column number.

## 2. Reverse two-way lookups in a cross-reference table [Excel 2016]

table

Row 2 contains variable and Column B contains another variable

The combination of 32 with 51 gives me 0.9 or cell D4

I need to report which 2 and B combinations give me values between 0.4 and 0.5. In this case, it would be 31-53 and 32-50.

I could rearrange this to be in 3 columns with the results on the last column and use something like below (The equation does not represent the example I gave.)

IFERROR(INDEX(array, SMALL(IF((min=data), ROW($B$2:$B$10)-1), ROW(A3)), COLUMN(A3)), " ")

This works but my excel table would have too many rows and the initial data set comes the other way so it would take some time to convert it. The original matrix is more compact.

I am trying to convert this equation but I am having trouble matching it to an array instead to just one column.

The following array formula returns multiple values from a cross-reference table if they meet specific criteria.

Array formula in cell H3:

If you prefer having the values in a cell each instead of concatenated values in one cell, go to this part of this article.

### 2.1 How to build an array formula

If you did above steps correctly excel automatically adds a beginning and ending curly bracket toÂ the formula, like this:

Don't enter these characters yourself.

### 2.2 Explaining array formula in cell G3

Note, the TEXTJOIN function works only in Excel 2016. Use this formula if you have an earlier version of Excel.

#### Step 1 - Check which values are larger than the condition in cell H1

The less than sign and equal sign are logical operators and can be combined, the result is a boolean value True or False.

$C$3:$E$5>=$H$1

becomes

{0.1, 0.7, 0.4; 0.5, 0.9, 0.8; 0.8, 0.6, 0.6}>=0.4

and returns

{FALSE, TRUE, TRUE; TRUE, TRUE, TRUE; TRUE, TRUE, TRUE}.

#### Step 2 - Check which values are smaller than the condition in cell H2

$C$3:$E$5<=$H$2

becomes

{0.1, 0.7, 0.4; 0.5, 0.9, 0.8; 0.8, 0.6, 0.6}<=0.5

and returns

{TRUE,FALSE,TRUE; TRUE,FALSE,FALSE; FALSE,FALSE,FALSE}.

#### Step 3 - Both conditions must be TRUE (AND-logic)

($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2)

becomes

{FALSE, TRUE, TRUE; TRUE, TRUE, TRUE; TRUE, TRUE, TRUE} * {TRUE,FALSE,TRUE; TRUE,FALSE,FALSE; FALSE,FALSE,FALSE}

and returns {0, 0, 1; 1, 0, 0; 0, 0, 0}.

#### Step 4 - Extract corresponding values from B3:B5 and C2:E2

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2), $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, {"31-50","31-51","31-53";"32-50","32-51","32-53";"33-50","33-51","33-53"},"")

and returns {"","","31-53";"32-50","","";"","",""},

#### Step 5 - Concatenate values using TEXTJOIN function

The TEXTJOINÂ function allows you to combine text strings from multiple cell ranges and also use delimiting characters if you want.

TEXTJOIN(*delimiter*,Â *ignore_empty*,Â *text1*,Â *[text2]*, ...)

TEXTJOIN(",",TRUE,{"","","31-53";"32-50","","";"","",""})

returns

"31-53. 32-50" in cell H3.

Link to two-way lookups in an index table:

Looking up data in a cross reference table

#### 2.3 Get excel *.xlsx file

Reverse two-way lookup in a cross reference table.xlsx

## 3. Reverse two-way lookups in a cross-reference table

This formula is for excel versions that don't have the TEXTJOIN function or if you prefer having the values in a cell each instead of concatenated values in one cell.

Array formula in cell G7:

Array formula in cell H7:

Array formula in cell I7:

### 3.1 Explaining formula in cell G7

#### Step 1 - Check which values are larger than the condition in cell H1

The less than sign and equal sign are logical operators and can be combined, the result is a boolean value True or False.

$C$3:$E$5>=$H$1

becomes

{0.45, 0.7, 0.4; 0.5, 0.9, 0.47; 0.8, 0.6, 0.42}>=0.4

and returns

{TRUE, TRUE, TRUE;TRUE, TRUE, TRUE;TRUE, TRUE, TRUE}.

#### Step 2 - Check which values are smaller than the condition in cell H2

$C$3:$E$5<=$H$2

becomes

{0.45, 0.7, 0.4; 0.5, 0.9, 0.47; 0.8, 0.6, 0.42}<=0.5

and returns

{TRUE, FALSE, TRUE; TRUE, FALSE, TRUE; FALSE, FALSE, TRUE}.

#### Step 3 - Both conditions must be TRUE (AND-logic)

($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2)

becomes

{TRUE, TRUE, TRUE;TRUE, TRUE, TRUE;TRUE, TRUE, TRUE} * {TRUE, FALSE, TRUE; TRUE, FALSE, TRUE; FALSE, FALSE, TRUE}

and returns {1, 0, 1;1, 0, 1;0, 0, 1}.

#### Step 4 - Create a sequence from 1 to n

The ROW function returns row numbers based on a cell reference.

ROW($B$3:$B$5)

returns {3; 4; 5}.

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(ROW($B$3:$B$5), ROW($B$3:$B$5))

becomes

MATCH({3; 4; 5}, {3; 4; 5})

and returns {1; 2; 3}.

#### Step 5 - Return corresponding row number if True

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(($C$3:$E$5<=$H$2)*($C$3:$E$5>=$H$1)*(G7=$B$3:$B$5) ,MATCH(COLUMN($C$2:$E$2), COLUMN($C$2:$E$2)), "")

becomes

IF({1, 0, 1;1, 0, 1;0, 0, 1}, {1; 2; 3}, "")

and returns {1,"", 1; 2, "", 2; "", "", 3}.

#### Step 6 - Extract k-th smallest row number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(*array*,Â *k*)

SMALL(IF(($C$3:$E$5<=$H$2)*($C$3:$E$5>=$H$1), MATCH(ROW($B$3:$B$5), ROW($B$3:$B$5)), ""), ROW(A1))

becomes

SMALL({1,"", 1; 2, "", 2; "", "", 3}, ROW(A1))

becomes

SMALL({1,"", 1; 2, "", 2; "", "", 3}, 1)

and returns 1.

#### Step 7 - Return value based on a row number

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array, [row_num], [column_num])

INDEX($B$3:$B$5, SMALL(IF(($C$3:$E$5<=$H$2)*($C$3:$E$5>=$H$1), MATCH(ROW($B$3:$B$5), ROW($B$3:$B$5)), ""), ROW(A1)))

becomes

INDEX($B$3:$B$5, 1)

amd returns 31.

### 3.2 Get excel *.xlsx file

Reverse-two-way-lookup-in-a-cross-reference-tablev3.xlsx

## 4. Reverse two-way lookup in a cross-reference table

The following array formulas return a single value from a cross-reference table.

Array formula in cell E15:

Array formula in cell E16:

### 4.1 Explaining array formula in cell E15

#### Step 1 - Check what values in cell range equals the value in E14 and return the smallest row

MIN(IF(B3:K12=E14, MATCH(ROW(B3:K12), ROW(B3:K12)), ""))

returns 5.

#### Step 2 -Â Return corresponding header value

=INDEX($A$3:$A$12, 5)

returns H.

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

### Lookups category

This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

### 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 […]

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

This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]

### Excel categories

### 32 Responses to “How to perform a two-dimensional lookup”

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

Oscar,

Hello again, I still have trouble with this excel

(remember?

B 1 2 3 4 5 6-20

bole1 24 27 10 43 63 45

bole2 25 09 98 12 56 32

bole3 33 12 39 00 23 11

You know, in reality

I have something like this

B 1 2 3 4 5

bole1 24|| 27|| 10|| 43|| 63

bole2 25|| 09|| 98|| 12|| 56

bole3 33|| 12|| 39|| 85|| 23

D 20 30 43 50

Doll1 200|||345||231||600

Doll2 124|||232||452||320

Do you know how can I combine these 2 tables with the same INDEX?

Thanks anyway

Russel,

My answer has two index functions.

Formula in cell C14:

Get the Excel 2007 file *.xlsx

russel.xlsx

I cannot thank you enough for this.Ether way I thank you very much...

But I have office 2003 and also

when I change the value x-axis for example,it appears #NAME? error...same as if I use the formula to my example

Oscar,

I think that the function IFERROR is not supported in Office 2003

Russel,

Excel 2003 formula:

Also, if the tables are more than 2, is the same type right?

Oscar,

You are GREAT!The God of the excel...i try to convert to if(iserror)

but I forget the third Index...witch i believe make the difference!

And I think with the If(iserror is more easy for above the 3 tables right?

Russel,

thanks.

The iferror function is easier.

Oscar,

Hello again!

Is there a "logic-form" for above 3 tables?Always with the if(iserror)?

Russel,

Two index functions:

=IF(ISERROR(formula_tbl_1)), formula_tbl_2, formula_tbl_1)

Three index functions:

=IF(ISERROR(formula_tbl_1)), IF(ISERROR(formula_tbl_2)), formula_tbl_3), formula_tbl_2), formula_tbl_1)

Oscar,

I try the function,now with 4 tables!And I guess that is something like this

=IF(ISERROR(formula_tbl_1)), IF(ISERROR(formula_tbl_2)), IF(ISERROR(formula_tbl_3)), formula_tbl_4),formula_tbl_3)formula_tbl_2), formula_tbl_1)

Right?

For one more time,thank you very much!

Russel,

Yes!

I removed some brackets.

Oscar,

Great, works like a charm!

One question more,is the "formula_tbl_1" an example-shortcut of writing this

INDEX(B2:F4, MATCH(C11, A2:A4, 0), MATCH(C12, B1:F1, 0))), INDEX(B7:E8, MATCH(C11, A7:A8, 0), MATCH(C12, B6:E6)

Or there is a way, to name your formula, like named ranges?

Russel,

I simplified the formula to make it easier to read.

But you could also create named ranges for each table, I tried and it works.

i am facing a problem with two tables -

Table 1

column1 column2 column3

row1 a 1 2

row2 b 3 4

row3 c 5 6

row4 d 7 8

Table 2

column1 column2 column3

row1 a a a

row2 b b b

row3 c d

row4 d

Now i'm trying to put a formula such that from table 2, if column 1 is referred, it should then refer to table 1 and pick corresponding values from either column 1 or 2 and add the values and give results - in above case, if from table 2, column 3 is selcted, then i shoudl get the result as (1+3 = 4 or 2+4 = 6 depending on what column is required to be referred in table 1.

Hi Oscar,

You are really amazing!

Thanks a lot

Hi Oscar,

I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., filled or not). Is there anyway to pull a list of names in a canned daily report based on the date and whether the cell is filled? In other words, I want to lookup the y-axis headers as opposed to the cross reference value.

Thanks,

Geoff

Geoff,

Great question!

Read this post: Shift schedule

[...] Excel, Search/Lookup, Templates on Aug.17, 2012. Email This article to a Friend Geoff asks:Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of [...]

[…] https://www.get-digital-help.com/2017/05/16/reverse-two-way-lookup-in-a-cross-reference-table/ […]

Hi Oscar,

Thank you very much.

Is there a way to report each combination with its corresponding results so in the example provided you would see

Column J Column K Column L

31 53 0.4

32 50 0.5

or

Column J Column K

31-53 0.4

32-50 0.5

Thanks Again.

Polar

(P.S Sorry this is a repost from the old thread. Just making sure I am not confusing things).

Polar,

Yes, it is possible. Read this.

Thank you!

[…] Tip! Read this post to doÂ Reverse two-way lookup in a cross reference table. […]

This is exactly what I have been looking for except for I need to have in the horizontal (x)cell say 2000 next to it 3000 and 4000 so on. And the same on the vertical (y) and cross reference it. but if I put in 2859 x value and 2679 in the y value. How do I do it so it goes up to the next highest value (3000) between the 2000 and 3000. I have 2003 excel. I have a paper chart and wanting to put it into a spreadsheet. Thanks Allan.

Alan White,

Try this:

Formula in cell C10:

=INDEX(C3:E5, MATCH(C9, B3:B5)+1, MATCH(C8, C2:E2)+1)

How would I go about looking up data in a cross-reference table.

I have the header row (i.e. 25) value and the column (mm) value and want to return the x/y value. i.e I have 0.25/X and 0.48/Y item and want 1.6 to be returned.

(mm) width 10~20 20.1~30 30.1~40 40.1~50

0.2~0.45 1.3 1.8 2.1 3.5

0.46~0.60 1.4 1.6 1.8 2.3

0.61~0.70 1.5 1.7 1.6 2.1

0.71~0.80 0.7 1.1 2.2 3.1

Sergio,

Formula in cell C10:

=INDEX(C3:F6,MATCH(C9,B3:B6)+1,MATCH(C8,C2:F2)+1)

Hi Oscar,

Not sure if you can help here:

How do I look up (search for) a value (text) in a table and return the cell reference. Example: search for the name "John" in B1:F24, and if found, return cell address, say D6. Preferably, I'd like to find "John" and return the only the ROW number, in my example 6. I could then use this ROW value in INDEX function.

Apologies, if this is simple, but i've almost lost it trying to figure it out for the past few days. Thanks.

Gerry,

No need to apologize, I am happy to help you out.

Try this array formula to get the row number:

=MIN(IF(B1:F24="John",ROW(B1:F24,""))

Press CTRL + SHIFT + ENTER to create an array formula.

Hello, First of All Thank you so much for sharing your Knowledge, makes a happier world. Second I want to do the opposite of this topic. And I do not have a clue if it is possible. I want to have a list of "things" with to values "X" and "Y" that will help as a coordinates. Then in a second sheet I want to have the first row labeled from "0" to "n" and the first column labeled from "0" to "n". In this second sheet I want to appear in the cell the name of the "Thing" in the square were is crossing the value of the column "x" and "y". The list will change all the time and will be given different values. And in an advance feature will be to calculate how far is the "thing 1" from "thing 2" on a straight line trajectory.

_______________

Thing | x | y |

---------------

house | 5 | 5 |

park | 1 | 1 |

---------------

Second sheet:

______________________

|park| | | | |

----------------------

| | | | | |

----------------------

| | | | | |

----------------------

| | | | | |

----------------------

| | | | |house|

----------------------

Thank you so much for your time and help.

Regards,

Art

If there are be the same data in y_header (i.e several 9) how i can extract a list of data?