## How to perform a two-dimensional lookup

*Article updated on March 25, 2018*

**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,3)

becomes

=INDEX(D4:K7,2,3)

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.

### Download excel sample file

looking-up-data-in-an-cross-ref-table

(Excel 97-2003 Workbook *.xls)

### Functions in this article

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

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

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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:

Download 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 [...]