Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data that matches exact column and row
hi.i ve got a data table where rows are width and columns are hight. how do i
get data from the cell that matches ceartin column and row,? I use vlookup, or index, and IF. but IF can be used only 7 times, i need to use it 16 times. thanx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data that matches exact column and row
Not very sure**, but drawing mainly from your subject line,
perhaps an example using INDEX & MATCH would help ? **in the absence of details (set-up, formulas used, etc) in your post A sample construct is available at: http://www.savefile.com/files/8246140 Retrieve Values from Table_Index n Match example.xls Assume a source table X in A1:E5 Table X 1111 1112 1113 1114 Name1 1009 1622 1966 1985 Name2 1234 1470 1758 1846 Name3 1874 1131 1566 1006 Name4 1777 1573 1325 1874 Assume inputs for the names / numbers (the col headers: 1111, 1112 ..) are made in A8:B11, viz: Input1 Input2 Value Name1 1112 ? Name2 1111 ? Name3 1114 ? Name4 1113 ? To extract the matching values from the table, we could put in C8, and copy down: =INDEX($B$2:$E$5,MATCH(A8,$A$2:$A$5,0),MATCH(B8,$B $1:$E$1,0)) Alternatively, with error trapping, albeit longer, we could put instead in C8 and copy down: =IF(COUNTA(A8:B8)<2,"", IF(OR(ISNA(MATCH(A8,$A$2:$A$5,0)),ISNA(MATCH(B8,$B $1:$E$1,0))),"No match found", INDEX($B$2:$E$5,MATCH(A8,$A$2:$A$5,0),MATCH(B8,$B$ 1:$E$1,0)))) The above would yield the results: Input1 Input2 Value Name1 1112 1622 Name2 1111 1234 Name3 1114 1006 Name4 1113 1325 The error traps will return blanks: "" for incomplete inputs, and "No match found" where either or both of the inputs 1/2 do not match the row/col headers. Without the error traps, we'd get #N/As for such cases. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "raraschek" wrote in message ... hi.i ve got a data table where rows are width and columns are hight. how do i get data from the cell that matches certain column and row,? I use vlookup, or index, and IF. but IF can be used only 7 times, i need to use it 16 times. thanx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data that matches exact column and row
Try something like
=INDEX(A1:H20,MATCH(row_value,A1:A20,0),MATCH(heig ht_value,A1:H1,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "raraschek" wrote in message ... hi.i ve got a data table where rows are width and columns are hight. how do i get data from the cell that matches ceartin column and row,? I use vlookup, or index, and IF. but IF can be used only 7 times, i need to use it 16 times. thanx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data that matches exact column and row
For two-dimensional lookups see:
http://www.cpearson.com/excel/lookups.htm -- Gary's Student "Bob Phillips" wrote: Try something like =INDEX(A1:H20,MATCH(row_value,A1:A20,0),MATCH(heig ht_value,A1:H1,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "raraschek" wrote in message ... hi.i ve got a data table where rows are width and columns are hight. how do i get data from the cell that matches ceartin column and row,? I use vlookup, or index, and IF. but IF can be used only 7 times, i need to use it 16 times. thanx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data that matches exact column and row
"Gary''s Student" wrote: thank you all, raraschek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|