Thread: How do I find where a column value and row value intersect?

I have a large spreadsheet that currently uses the VLOOKUP function. I want
to add more data to my "Cheat Sheet" so that I can have a VLOOKUP pull up the
value on the vertical access then cross reference to an HLOOKUP that looks up
the value on the horizontal access, and the result returns the value where
both criteria are met in the middle.

For example, if the lookup value for the VLOOKUP is in cell A7 (along the
vertical axis/left most column) on the original table array, and the lookup
value for the HLOOKUP is in cell D1 (along the horizontal axis/top most row),
I want the value returned to be the value that is in D7 (where the two cell
values meet on the grid).

Assuming table is A1:H9 and lookup value for vertical axis in Col A is in
cell A15, and lookup value for Top axis in Row 1 is in cell A16

=INDEX($A$1:$I$9,MATCH(A15,A1:A9),MATCH(A16,A1:I1))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Amy" <Amy@discussions.microsoft.com> wrote in message
news:F2D46373-BC45-449A-9E3F-5E2E68B85FF5@microsoft.com...
> I have a large spreadsheet that currently uses the VLOOKUP function. I
want
> to add more data to my "Cheat Sheet" so that I can have a VLOOKUP pull up
the
> value on the vertical access then cross reference to an HLOOKUP that looks
up
> the value on the horizontal access, and the result returns the value where
> both criteria are met in the middle.
>
> For example, if the lookup value for the VLOOKUP is in cell A7 (along the
> vertical axis/left most column) on the original table array, and the
lookup
> value for the HLOOKUP is in cell D1 (along the horizontal axis/top most
row),
> I want the value returned to be the value that is in D7 (where the two
cell
> values meet on the grid).
>
>

=VLOOKUP(A7,Table,MATCH(D1,INDEX(Table,1,0),0),0)

where Table is assumed to include the topmost row. The match-type
(range_lookup) is set 0 (FALSE) in order to effect exact matching of the
lookup values.

Amy wrote:
> I have a large spreadsheet that currently uses the VLOOKUP function. I want
> to add more data to my "Cheat Sheet" so that I can have a VLOOKUP pull up the
> value on the vertical access then cross reference to an HLOOKUP that looks up
> the value on the horizontal access, and the result returns the value where
> both criteria are met in the middle.
>
> For example, if the lookup value for the VLOOKUP is in cell A7 (along the
> vertical axis/left most column) on the original table array, and the lookup
> value for the HLOOKUP is in cell D1 (along the horizontal axis/top most row),
> I want the value returned to be the value that is in D7 (where the two cell
> values meet on the grid).
>
>