Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value matching vertical and horizontal input
Hello
Can you please help me with the following. I need to lookup up a value in the cell A1 of sheet 1 and return the value in sheet 2 of the column next to the the column with a heading matching the value of B2 in sheet 1. For example. Sheet 1 Ref Type 1 Man 2 Woman 3 Man 4 Child Sheet 2 Ref Man Man type Woman Woman type Child Child type 1 Peter x Sarah y Lily x 2 John y Jane z Max x 3 Bob y Jane x Alice x 4 Fred z Jane x Ruby y So I would expect the returned value on sheet 1 to be: Ref Type Result 1 Man x 2 Woman z 3 Man y 4 Child y Any help on this one greatly appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value matching vertical and horizontal input
Try this.
=VLOOKUP(A2,Sheet2!$A$1:$G$5,VLOOKUP(B2,{"child",7 ;"woman",5;"man",3},2,FALSE),FALSE) Mike wrote in message ... Hello Can you please help me with the following. I need to lookup up a value in the cell A1 of sheet 1 and return the value in sheet 2 of the column next to the the column with a heading matching the value of B2 in sheet 1. For example. Sheet 1 Ref Type 1 Man 2 Woman 3 Man 4 Child Sheet 2 Ref Man Man type Woman Woman type Child Child type 1 Peter x Sarah y Lily x 2 John y Jane z Max x 3 Bob y Jane x Alice x 4 Fred z Jane x Ruby y So I would expect the returned value on sheet 1 to be: Ref Type Result 1 Man x 2 Woman z 3 Man y 4 Child y Any help on this one greatly appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value matching vertical and horizontal input
On 7 Mar, 08:12, "mikebres" <mike com1 at comcast dot net wrote:
Try this. =VLOOKUP(A2,Sheet2!$A$1:$G$5,VLOOKUP(B2,{"child",7 ;"woman",5;"man",3},2,FAL*SE),FALSE) Mike wrote in message ... Hello Can you please help me with the following. I need to lookup up a value in the cell A1 of sheet 1 and return the value in sheet 2 of the column next to the the column with a heading matching the value of B2 in sheet 1. For example. Sheet 1 Ref * Type 1 * *Man 2 * *Woman 3 * *Man 4 * *Child Sheet 2 Ref * Man * Man type * Woman * Woman type * Child * Child type 1 * * Peter * x * * * * * * * Sarah * * *y * * * * * * * * * Lily x 2 * * John * *y * * * * * * * Jane * * * *z * * * * * * * * * Max * *x 3 * * Bob * * y * * * * * * * Jane * * * *x * * * * * * * * * Alice x 4 * * Fred * *z * * * * * * * Jane * * * *x * * * * * * * * * Ruby y So I would expect the returned value on sheet 1 to be: Ref * *Type * * *Result 1 * * * Man * * * x 2 * * * Woman *z 3 * * * Man * * * y 4 * * * Child * * *y Any help on this one greatly appreciated- Hide quoted text - - Show quoted text - Thanks Mike - but this was an example - I've actually got a whole heap of "Type" so wanted a formula that picked up what was in column B sheet 1 and look for it as a column heading in Sheet 2 rather than type in the possible headings. I've been trying with MATCH but not having any sucess. Have you got any other suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value matching vertical and horizontal input
Try this:
=VLOOKUP(A2,Sheet2!$A:$G,MATCH(B2,Sheet2!$1:$1,0)+ 1,FALSE) assuming your first row of data on Sheet1 is in row 2, and your headings on Sheet2 are in row 1. Hope this helps, Hutch " wrote: Hello Can you please help me with the following. I need to lookup up a value in the cell A1 of sheet 1 and return the value in sheet 2 of the column next to the the column with a heading matching the value of B2 in sheet 1. For example. Sheet 1 Ref Type 1 Man 2 Woman 3 Man 4 Child Sheet 2 Ref Man Man type Woman Woman type Child Child type 1 Peter x Sarah y Lily x 2 John y Jane z Max x 3 Bob y Jane x Alice x 4 Fred z Jane x Ruby y So I would expect the returned value on sheet 1 to be: Ref Type Result 1 Man x 2 Woman z 3 Man y 4 Child y Any help on this one greatly appreciated |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value matching vertical and horizontal input
Any help on this one greatly appreciated- Hide quoted text - - Show quoted text - Thanks Mike - but this was an example - I've actually got a whole heap of "Type" so wanted a formula that picked up what was in column B sheet 1 and look for it as a column heading in Sheet 2 rather than type in the possible headings. I've been trying with MATCH but not having any sucess. Have you got any other suggestions? You could change the inner vlookup and have it reference a helper table. The helper table would be a list of the heading names you want use and the column number of the heading. It would look something like this =VLOOKUP(A2,Sheet2!$A$1:$G$5,VLOOKUP(B2,TypeList,2 ,FALSE),FALSE) where TypeList would be Type Column Man 3 Woman 5 Child 7 Other 9 etc. Part of the problem is the data isn't oganized very well. Can you reorganize your data? If so it would be easier to work with it if you could set it up like this: Ref Name Type Value 1 Peter Man x 2 John Man y 1 Sue Woman x 1 Alice Child x 5 Mark Man z then you could use a whole host of methods to get your data. Such as array formulas, sumproduct, pivot tables, the query. For example to use formulas, you could either create range names or reference the cells directly. With range names in sumproduct it would look like this: Ref Type Result 1 Man =sumproduct(--(Ref=$A2),--(Type=$B2), Value)) with cell reference it would be Ref Type Result 1 Man =sumproduct(--($A$2:$A$500=$A2),--($B$2:$B$500=$B2), $C$2:$C$500)) or with array formulas it would be Ref Type Result 1 Man = SUM(IF(Ref=$A2),IF(Type=$B2,Value)) then press CTRL SHIFT ENTER while still in the formula bar. Mike |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value matching vertical and horizontal input
On 7 Mar, 15:40, Tom Hutchins
wrote: Try this: =VLOOKUP(A2,Sheet2!$A:$G,MATCH(B2,Sheet2!$1:$1,0)+ 1,FALSE) assuming your first row of data on Sheet1 is in row 2, and your headings on Sheet2 are in row 1. Hope this helps, Hutch " wrote: Hello Can you please help me with the following. I need to lookup up a value in the cell A1 of sheet 1 and return the value in sheet 2 of the column next to the the column with a heading matching the value of B2 in sheet 1. For example. Sheet 1 Ref * Type 1 * *Man 2 * *Woman 3 * *Man 4 * *Child Sheet 2 Ref * Man * Man type * Woman * Woman type * Child * Child type 1 * * Peter * x * * * * * * * Sarah * * *y * * * * * * * * * Lily x 2 * * John * *y * * * * * * * Jane * * * *z * * * * * * * * * Max * *x 3 * * Bob * * y * * * * * * * Jane * * * *x * * * * * * * * * Alice x 4 * * Fred * *z * * * * * * * Jane * * * *x * * * * * * * * * Ruby y So I would expect the returned value on sheet 1 to be: Ref * *Type * * *Result 1 * * * Man * * * x 2 * * * Woman *z 3 * * * Man * * * y 4 * * * Child * * *y Any help on this one greatly appreciated- Hide quoted text - - Show quoted text - Hutch - thankyou, it's worked a charm. Mike, thanks for all your suggestions on this one as well - much appreciated. Megan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
horizontal lines to vertical | Excel Discussion (Misc queries) | |||
Vertical to Horizontal | Excel Discussion (Misc queries) | |||
Arrays - Horizontal or Vertical | Excel Worksheet Functions | |||
Vertical to horizontal | Excel Discussion (Misc queries) |