software

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

Not very sure**, but drawing mainly from your subject line,
perhaps an example using INDEX amp; MATCH would help ?
**in the absence of details (set-up, formulas used, etc) in your post

A sample construct is available at:
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)lt;2,quot;quot;,
IF(OR(ISNA(MATCH(A8,$A$2:$A$5,0)),ISNA(MATCH(B8,$B $1:$E$1,0))),quot;No match
foundquot;,
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: quot;quot; for incomplete inputs, and quot;No match
foundquot; 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
savefile.com/projects/236895
xdemechanik
---
quot;raraschekquot; gt; wrote in message
...
gt; hi.i ve got a data table where rows are width and columns are hight. how
do i
gt; get data from the cell that matches certain column and row,? I use
vlookup,
gt; or index, and IF. but IF can be used only 7 times, i need to use it 16
times.
gt; thanx
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)

quot;raraschekquot; gt; wrote in message
...
gt; hi.i ve got a data table where rows are width and columns are hight. how
do i
gt; get data from the cell that matches ceartin column and row,? I use
vlookup,
gt; or index, and IF. but IF can be used only 7 times, i need to use it 16
times.
gt; thanx
For two-dimensional lookups see:www.cpearson.com/excel/lookups.htm
--
Gary's Studentquot;Bob Phillipsquot; wrote:

gt; Try something like
gt;
gt; =INDEX(A1:H20,MATCH(row_value,A1:A20,0),MATCH(heig ht_value,A1:H1,0))
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;raraschekquot; gt; wrote in message
gt; ...
gt; gt; hi.i ve got a data table where rows are width and columns are hight. how
gt; do i
gt; gt; get data from the cell that matches ceartin column and row,? I use
gt; vlookup,
gt; gt; or index, and IF. but IF can be used only 7 times, i need to use it 16
gt; times.
gt; gt; thanx
gt;
gt;
gt;



quot;Gary''s Studentquot; wrote:

thank you all,
raraschek