Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |