How to perform a two-dimensional lookup
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.
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 […]
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
Excel categories
27 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.
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 [...]
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?