Complex Look up

R

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
 
M

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
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top