Looking up data in a cross reference table in excel
Question: How would I go about looking up data in an 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.3
thanks
Answer:
Formula in F1:
Named ranges
tbl (C4:J7)
y_value (B1)
x_value (D1)
y_header (B4:B7)
x_header (C3:J3)
What is named ranges?
How to customize formula to your workbook
Change named ranges.
Download excel sample file for this tutorial.
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
Related posts:
Excel udf: Looking up data in multiple cross reference tables
Vlookup visible data in a table and return multiple values in excel
Return row reference of largest to smallest
Calculate average of last 10 data (possible blanks) in excel
Recommended categories
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 [...]