E-Mail 'How to perform a two-dimensional lookup' To A Friend
Email a copy of 'How to perform a two-dimensional lookup' to a friend
Loading ... Loading ...38 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 [...]
Says "Cannot open specified file"... Am I doing something wrong?
chrisham,
Make sure you saved the file as "Locate a particular cell in a table.xlsx".
or change the array formula to
=HYPERLINK("[yourfilename]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0), MATCH(Sheet1!B2, Table1[#Headers], 0)), IF(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))=0, "Insert rate here", INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))))
Oscar -
Can you change the formula so that the hyperlink address will return the correct value if the source table does not start at A1? For example, my lookup range starts at $L$20 and the value may be at $N$22 but the ADDRESS function for the hyperlink returns $C$3 since it's the 3rd row and 3rd column in the lookup range.
Got it (at least one way)...
Match criteria are on worksheet Formulas!D99 and !E99
Lookup range is on worksheet Sources!L11:N62 not including column headers in row 10.
=HYPERLINK(CELL("address", OFFSET(Sources!$L$11,MATCH(Formulas!$D99,Sources!$L$11:$L$62,0)-1, MATCH(Formulas!$E99, Sources!$L$10:$N$10, 0)-1)), IF(OFFSET(Sources!$L$11, MATCH(Formulas!$D99, Sources!$L$11:$L$62, 0)-1, MATCH(Formulas!$E99, Sources!$L$10:$N$10, 0)-1)=0, "No value found", OFFSET(Sources!$L$11, MATCH(Formulas!$D99, Sources!$L$11:$L$62, 0)-1, MATCH(Formulas!$E99, Sources!$L$10:$N$10, 0)-1)))
Excel forces the use of the CELL-OFFSET combination to return the actual address of the cell rather than its relative row and column position in the range. But it has the advantage that the CELL function returns the full path of the file and worksheet so you don't have to put them in square brackets for the HYPERLINK argument if you're in the same spreadsheet.
Now if only Excel would come up with an IFZERO function similar to IFERROR so that you don't have to duplicate your formula in the logical test and the outcome! Even better a generic
IFVALUE(test_condition,if_true,if_false)
So testing the above code for a zero value would give IFVALUE(0,,"Insert rate here"). I tried a UDF but it wouldn't refresh as nicely as a native Excel function.
GMF,
Can you change the formula so that the hyperlink address will return the correct value if the source table does not start at A1?
Great question!
I created this formula:
=HYPERLINK("[Locate-a-particular-cell-in-a-table.xlsx]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0)+CELL("row", Table1)-2, MATCH(Sheet1!B2, Table1[#Headers], 0)+CELL("col", Table1)-1), IF(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))=0, "Insert rate here", INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))))
Get the Excel *.xlsx file
Locate-a-particular-cell-in-a-tablev2.xlsx
Thanks for sharing!
GMF,
Now if only Excel would come up with an IFZERO function similar to IFERROR so that you don't have to duplicate your formula in the logical test and the outcome! Even better a generic
IFVALUE(test_condition,if_true,if_false)
Another great question!
Formula:
=HYPERLINK("[Locate-a-particular-cell-in-a-table.xlsx]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0)+CELL("row", Table1)-2, MATCH(Sheet1!B2, Table1[#Headers], 0)+CELL("col", Table1)-1), IFERROR(1/(1/(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0)))), "Insert rate here"))
Get the Excel *.xlsx file
Locate-a-particular-cell-in-a-tablev3.xlsx
The IFERROR(1/0) test is good for numeric values but generates the error option if you're returning text (for example, a lookup of tax rates could provide a text range ["1.6% - 7.4%"] for 2013 Idaho state taxes whereas Illinois has a flat 5% formatted as a number). I guess you'd have to tailor the error handling to the type of data you expect to return.
Mere quibbles considering the usefulness of the overall post!
[…] https://www.get-digital-help.com/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!
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?