Lookup to find cell reference
 

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

Lookup to find cell reference
 

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

On Jan 10, 4:30*pm, jlclyde wrote:
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

Lookup to find cell reference
 

See Double Lookups in:

http://www.cpearson.com/excel/TablesAndLookups.aspx
--
Gary''s Student - gsnu2007c


"jlclyde" wrote:

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

Lookup to find cell reference
 

On Jan 10, 9:30*pm, jlclyde wrote:
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(B2:D4,MATCH(A7,A2:A4),MATCH(B7,B1:D1))


Hope this helps

Regards
Anirudh

R2 C1 D

Lookup to find cell reference
 

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

Lookup to find cell reference
 

Dave ??



On Jan 10, 4:55*pm, jlclyde wrote:
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

Lookup to find cell reference
 

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
InsertNameCreate, 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

On Thu, 10 Jan 2008 08:30:45 -0800 (PST), jlclyde
wrote:

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