Lookup to find cell reference

[jlclyde]

I have a table with 10 rows and 10 columns of data. Each row and
column has a unique heading. columns are catalogs and rows are reason
codes. In another area on the spreadsheet I have cells that have the
reason code and catalog. What I need is a way to use those references
to lookup what is in the table. Maybe a match() or index? I am
unsure.

Thanks,
Jay

 

[Pete_UK]

You can do it with INDEX(table,MATCH(...),MATCH(...))

Describe your data layout more fully, then I can fill in the other
details for you.

Hope this helps.

Pete

 

[Gary''s Student]

See Double Lookups in:

http://www.cpearson.com/excel/TablesAndLookups.aspx

 

[Ag]

I have a table with 10 rows and 10 columns of data.  Each row and
column has a unique heading.  columns are catalogs and rows are reason
codes.  In another area on the spreadsheet I have cells that have the
reason code and catalog.  What I need is a way to use those references
to lookup what is in the table.  Maybe a match()  or index?  I am
unsure.

Thanks,
Jay

Hi Jay,

Assuming a structure as given below you can use the Index with 1
match for row and second match for the column -refer formula below

3x3matrix C1 C2 C3
R1 A Q E
R2 D F G
R3 G H J

some other area of sheet
R2 C1 =INDEX(B24,MATCH(A7,A2:A4),MATCH(B7,B11))


Hope this helps

Regards
Anirudh

R2 C1 D

 

[jlclyde]

I did not think of Match and Index until I was about done with my
original post. I played aorund with it and came up with the same
thing Dave had. Thank you all for your help. I looked at the
pearsons site and found some very interesting things for future
projects.

Thanks,
Jay

 

[Pete_UK]

Dave ??

 

[Gord Dibben]

One method using created names.

Example only for a mileage chart....adjust ranges and labels.

You have a table A1:J10

Leave A1 blank.

Say A2:A10 contains city and B1:J1 contains city1

e.g

A2 = Quebec
B1 = Quebec1

A3 = Ottawa
C1 = Ottawa1

A4 = Timmins
D1 = Timmins1

B2:J10 contains mileages.

One method is to select the entire table A1:J10 then choose
Insert>Name>Create, and select top row and left column.

Then use the intersect functionality:

=city city1

In above example =quebec ottawa1

This will return the value of the cell at the intersection of city and city1


Gord Dibben Excel MVP