Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Look up
In sheet1, I have the following table :
Row A B C D Col3 Blank Scen1 Scen2 Scen3 Col4 Smith 12 8 9 Col5 Jones 16 3 2 etc Cell A2 of sheet2 will either be Scen1, Scen 2 or Scen3. The rest of column A in sheet2 has exactly the same names (and in the same order) as sheet1. I'd like to create a formula that, for each name in sheet2, looks at the contents sheet2!a2 and, in row B puts in the appropriate value from sheet1. As an example, if sheet2!a2 contained Scen1, then the value to be reported against Smith would be 8. Can SKS help on the syntax please |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Look up
Hi,
I have assumed our list of names start in A3 on Sheet 2. Try this in B3 and drag down =SUMPRODUCT((Sheet1!$B$1:$D$1=Sheet2!$A$2)*(Sheet1 !$A$2:$A$20=A3)*(Sheet1!$B$2:$D$20)) Mike "RobFJ" wrote: In sheet1, I have the following table : Row A B C D Col3 Blank Scen1 Scen2 Scen3 Col4 Smith 12 8 9 Col5 Jones 16 3 2 etc Cell A2 of sheet2 will either be Scen1, Scen 2 or Scen3. The rest of column A in sheet2 has exactly the same names (and in the same order) as sheet1. I'd like to create a formula that, for each name in sheet2, looks at the contents sheet2!a2 and, in row B puts in the appropriate value from sheet1. As an example, if sheet2!a2 contained Scen1, then the value to be reported against Smith would be 8. Can SKS help on the syntax please |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Look up
Mike,
Won't that fail with the different array shapes inside SUMPRODUCT? Here's a lookup for Sheet2, cell B3, can be copied down: =VLOOKUP(A3,Sheet1!$A$3:$D$100,MATCH($A$2,Sheet1!$ A$3:$D$3,0),0) RobFJ, It looks like you are saying the column headers on Sheet1 are in row 3, you may have to adjust the ranges if I am misunderstanding you. You seem to have rows and columns confused. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex IF | Excel Discussion (Misc queries) | |||
Bit of a complex SUM | Excel Worksheet Functions | |||
Complex sum | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Complex sum | Excel Discussion (Misc queries) |