match and display

[gootroots]

example;

sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values
sheet1;B2:B10 contains no unique values
sheet2:B1 contains a value

I need a formula that will return a match to correspond with sheet2;B1 and
sheet1;B2:B10 and if found return it in sheet2;B2:B10
Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match

Thank you if you can be of help.

 

[Luke M]

Assuming you are dealing with numbers, I believe this will work (place into
Sheet2!B2):

=SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),(Sheet1!$B$2:$B$10))

Formula looks up A2 in Sheet1, and if corresponding value in Sheet1 column B
= Sheet2!B2, returns value from Sheet1 column B.

 

[gootroots]

Actually Luke M I should have stated text values not numbers

 

[Luke M]

Ah. Need to add a little more then...

=INDEX(Sheet1!$B:$B,SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),ROW(Sheet1!$B$2:$B$10)))

 

[gootroots]

Not quite there yet Luke!

Let me try another example:

Sheet1 and Sheet2;
A2 = cat
A3 = dog
A4 = goldfish
A5 = hampster

Sheet1;
D3 = food
F5 = food

Sheet2;
B1 = food
B2 to B5 will contain the formula

Now the formula will look in sheet1 and will return food if applicable.
The result for this example would be:

sheet2;
cat
dog = B3 = food
goldfish
hampster = B5 = food

 

[Luke M]

Thanks for the example.

In Sheet2!B2
=IF(COUNTIF(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A$2:$A$10,0),0,1,5),B$1)>0,B$1,"")

Inside the OFFSET function, the last number determines how many columns to
check (currently set to look at B:F). This formula will work even if the two
lists in A2:A5 are not in identical order. If your guaranteed that the lists
will always be exactly the same, can use simpler formula:

=IF(COUNTIF(Sheet1!$B2:$F2,B$1)>0,B$1,"")

Do either of these work for you?