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