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

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

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

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

data that matches exact column and row
 

"Gary''s Student" wrote:

thank you all,
raraschek