Excel 2003 - Lookup type of question

[Fran]

I want to lookup and return the cell (on a different spreadsheet) where the
Row Header and the Column Header Match...... If A50 and Colum ZZ were both
named Orange, I want to return THAT specific cell into a spreadsheet.....
Orange in the column header and Orange on the road header - BRING ME THE
CELL.....

What formula or lookup type thing do I use?

 

[Eduardo]

Hi Fran,
Take a look at Debra web

http://www.contextures.com/xlFunctions03.html

if this helps please click yes, thanks

 

[Shane Devenshire]

Suppose you want to find the text "Orange" which you have entered in A100
within the range A1:Z26:

=INDEX(B2:Z26,MATCH(A100,B2:B26,0),MATCH(A100,B1:Z1,0))

modify as needed.

 

[Fran]

I'll check this out tomorrow.... the problem is that the information on sheet
one is DYNAMIC and changes depending on input. I wanted whatever is typed in
there to be referenced by Column and Row Headings. I'll try it tomorrow
though. When I search on the Excel site, it pointed me to an ADD-IN for
24.95 that would seem to do the trick. I did create a work around for my
dilema. (not explained here)

On Page one (which will be a form), there are two fields (like a 'from and
to' referencing different centers people drive to). On page 2, it shows
different centers (From; To; Mileage). I wanted page one to be an input page
and have the mileage appear on page 1 when they input their cities.

My setup was: (example) 10 cities as column headers, and EXACT same order
of cities as row headers. Then the cells reflected the mileage (i.e. clay
might be a column header in B) Maybe Geneva is a Row header on Row 32
(distance between Clay and Geneva).... Therefore B32 would have an input of
mileage (which when someone INPUT those cities on page one (in a dropdown
list)..... The mileage would be reflected automagically in an empty cell
(with a formula in it to 'get that cell' from page 2. Hope I'm explaining it
right. That way I could calculate mileage for reimbursement for people to
fill out.