Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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). |
#2
|
|||
|
|||
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" wrote in message ... 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). |
#3
|
|||
|
|||
=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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|