Multicondition Search by Row, with Text result

[Hartke]

Hello all,


I have a large set of data where I want to find a text result where two results are aligned in the same row. I would normally use SUMPRODUCT here but the field I am looking to have returned is text.


In the example below I would like to know who the owner is of Product 13, color Red. The answer I'm looking for is "Kamal."


ProductID Color Owner

12 Red Bob

12 Orange Jodi

13 Red Kamal

13 Orange Leon

14 Red Bob


I also looked at INDEX/MATCH but this seems to require an intersection between column and row which isn't the case here.


Thank you in advance for all suggestions!

 

[SirJB7]

Hi, Hartke!


Assuming this is your worksheet structure:

-----

[pre]

ProductID	Color	Owner		ProductID:	13
12		Red	Bob		Color:		Red
12		Orange	Jodi		Owner:		Kamal
13		Red	Kamal
13		Orange	Leon
14		Red	Bob

[/pre]
-----

in F3 type this array formula:

=INDICE(A2:C6;COINCIDIR(F1&F2;A2:A6&B2:B6;0);3) -----> =INDEX(A2:C6,MATCH(F1&F2,A2:A6&B2:B6,0),3)

Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


Regards!

 

[Hartke]

You, SirJB7, are magnificient. Thank you so much.

 

[SirJB7]

Hi, Hartke!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: If I were you I'd improve the ranges changing them by dynamic named ranges so as to forget about adding rows and adjusting formulas.