Lookup

[Simon]

I have a table of data 281 Rows x 48 Columns.
I want to return data from anywhere in the table dependant
upon certain conditions.
In Lotus 123 the @XINDEX function returned the contents of
a cell specified by column, row and sheet heading. I
specify the column and row headings and XINDEX returns the
data from the cell where the column and row meet.
How do i do this in Excel ?
Please help.

Regards,

Simon.

 

[JE McGimpsey]

One way:

Name the table, say, "Table". Assume your target row heading is in cell
B1 and your target column heading in B2

=INDEX(Table, MATCH(B1,INDEX(Table,0,1),FALSE),
MATCH(B2,INDEX(Table,1,0),FALSE))


If you're In article <[email protected]>,

 

[Frank Kabel]

Hi Simon

if X1 contains your column lookup value and Y1 your row lookup value
and your lokup range is A1:G100 use
=INDEX(A1:G100,MATCH(Y1,A1:A100,0),MATCH(X1,A1:G1,0))

 

[Bernie Deitrick]

Simon,

Assuming your table starts in cell A1:

=INDEX(A1:AV282,MATCH(???,A:A,FALSE),MATCH(????,1:1,FALSE))

Replace the first ??? with what you are looking for in the row headings (or
a cell reference), and the second ??? with what you are looking for in the
column headings.

If you want the sheet name to also be a variable, then you would need to
change the A1:AV282 to an INDIRECT function incorporating the cell reference
with the sheet name...

HTH,
Bernie
MS Excel MVP