Lookup with multiple variable

[meami111]

I have the following sample database starting with A1 as the 1st column heading:

Colour	Shape	Taste	Blank	Colour	Shape	Taste
Red	Sphere	Sweet		Red	Sphere
Blue	Triangle	Bitter		Blue	Triangle
Green	Square	Hot		Green	Square
Yellow	Rectangle	Tangy		Yellow	Rectangle
Blue	Sphere	Sweet		Blue	Sphere
Green	Triangle	Bitter		Green	Triangle
Yellow	Sphere	Hot		Yellow	Sphere
Red	Triangle	Bitter		Red	Triangle
Blue	Square	Hot		Blue	Square
Green	Rectangle	Tangy		Green	Rectangle

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

I want to find out the Lookup value for Taste in column G. Will sumproduct/sumifs work here? If not how can we get the look up value?

Thanks in advance for your help

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$11,MATCH($E2,IF($B$2:$B$11=$F2,$A$2:$A$11),0)),"no found")

 

[meami111]

Hi Aladin

Can you please tell what is to be done for the following instruction
"In G2 control+shift+enter, not just enter, and copy down"

I selected the G column with Ctrl+Shft+Enter and then pasted the formula in G2 and then again pressed Ctrl+Shft+Enter. It is copying the 1st result in all the rows.

Looking forward to your support

Thanks

 

[Aladin Akyurek]

Type the formula in G2. Then press down the control and the shift keys at the same time while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

 

[meami111]

Dear Aladin

Have tried put the array execution suggested by you. While it is working for other formulas, for the one suggested by you, I am only getting the first result repeated in all cells. Can you please check at your end if the desired result is coming?

Thanks

 

[Aladin Akyurek]

Dear Aladin

Have tried put the array execution suggested by you. While it is working for other formulas, for the one suggested by you, I am only getting the first result repeated in all cells. Can you please check at your end if the desired result is coming?

Thanks

See the workbook which implements the suggestion: Dropbox - meami111 Lookup with multiple variable.xlsx

 

[meami111]

See the workbook which implements the suggestion: Dropbox - meami111 Lookup with multiple variable.xlsx

Thanks a lot Aladin !! Issue resolved

Regards

 

[Aladin Akyurek]

Thanks a lot Aladin !! Issue resolved

Regards

You are welcome.