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

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

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

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

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

Use the img tag, like this: <img src="Insert pic link here">

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

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

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)