V lookup and H lookup together
 

Is it possible to do a v lookup and an h lookup at the same time? For
example I want to find the data that matches the row and column of the cell
I'm in?

V lookup and H lookup together
 

=INDEX(data,rownumber,colnumber)

data is a 2D array. row- and colnumber are either supplied as
constants or are computed by MATCH, if you need to locate row and
column by looking up.

HTH
Kostis Vezerides

On Feb 15, 6:54 pm, Sissy wrote:
Is it possible to do a v lookup and an h lookup at the same time? For
example I want to find the data that matches the row and column of the cell
I'm in?

V lookup and H lookup together
 

Hi Sissy,

Let's say your Horizontal keys are in B1:E1 and your Vertical keys in A2:A5. So your data is in B2:E5

Your Formula:

=INDEX(B2:E5;MATCH(H1;A2:A5;0);MATCH(G1;B1:E1;0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Sissy" wrote in message ...
| Is it possible to do a v lookup and an h lookup at the same time? For
| example I want to find the data that matches the row and column of the cell
| I'm in?

V lookup and H lookup together
 

Hi Niek,
Thank you, I'm trying this, but what's H1 and G1 in your formula? Where
does it reference the other spreadsheet I'm trying to get data from? Say
this is the sheet that contains data:
01/01/2007 02/01/2007 02/14/2007 02/20/2007
Blue Shirts 1000 1200 1100 1300
Blue Pants 50 75 60 65
Red Shirts 500 700 400 300
Red Pants 20 25 15 10

And I want to fill in this spreadsheet in the appropriate places based on
the description and date:

01/01/2007 01/15/2007 02/01/2007 02/15/2007
Red Pants
Blue Pants
Red Shirts
Blue Shirts

Thank you! -Sissy

"Niek Otten" wrote:

Hi Sissy,

Let's say your Horizontal keys are in B1:E1 and your Vertical keys in A2:A5. So your data is in B2:E5

Your Formula:

=INDEX(B2:E5;MATCH(H1;A2:A5;0);MATCH(G1;B1:E1;0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Sissy" wrote in message ...
| Is it possible to do a v lookup and an h lookup at the same time? For
| example I want to find the data that matches the row and column of the cell
| I'm in?

V lookup and H lookup together
 

H1 is the row key to look for, G1 the column key
If you don't have exact matches for the horizontal key (which seems to be the case), change the 0 in the second MATCH function to
1.
Just use it as an example, then apply to your sheets with references to other sheets

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Sissy" wrote in message ...
| Hi Niek,
| Thank you, I'm trying this, but what's H1 and G1 in your formula? Where
| does it reference the other spreadsheet I'm trying to get data from? Say
| this is the sheet that contains data:
| 01/01/2007 02/01/2007 02/14/2007 02/20/2007
| Blue Shirts 1000 1200 1100 1300
| Blue Pants 50 75 60 65
| Red Shirts 500 700 400 300
| Red Pants 20 25 15 10
|
| And I want to fill in this spreadsheet in the appropriate places based on
| the description and date:
|
| 01/01/2007 01/15/2007 02/01/2007 02/15/2007
| Red Pants
| Blue Pants
| Red Shirts
| Blue Shirts
|
| Thank you! -Sissy
|
| "Niek Otten" wrote:
|
| Hi Sissy,
|
| Let's say your Horizontal keys are in B1:E1 and your Vertical keys in A2:A5. So your data is in B2:E5
|
| Your Formula:
|
| =INDEX(B2:E5;MATCH(H1;A2:A5;0);MATCH(G1;B1:E1;0))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Sissy" wrote in message ...
| | Is it possible to do a v lookup and an h lookup at the same time? For
| | example I want to find the data that matches the row and column of the cell
| | I'm in?
|
|
|