Complex Look up

[RobFJ]

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

 

[Mike H]

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

 

[Spiky]

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.