Index name of a product

[Med4040]

Hi guys,

First of all, my apologies because I am attaching a picture. The excel add-in to upload files didn't seem to work with me

I have this table of products. Some products have one active ingredient and some have more than one.

I need help with a formula (in another table) that finds the name of the active ingredient that has the highest score
For example, for product (row 78) there are two active ingredient, so the formula would give me "Hydrochlorothiazide"

Thank you

[/IMG]

 

[Aladin Akyurek]

Does this succeed on your data?

Control+shift+enter, not just enter:

=INDEX($C$2:$C$500,MATCH(MAX(IF(ISNUMBER(SEARCH("gardia",$B$2:$B$500)),$Q$2:$Q$500)),IF(ISNUMBER(SEARCH("gardia",$B$2:$B$500)),$Q$2:$Q$500),0))

 

[Med4040]

Thanks Aladin,

I should have mentioned that I am looking for any product. The other table I referred to, in my first thread, is on another tab and looks like this
So, row 4 should show me the active ingredient for the product in row 1 that meet my criteria.

[/IMG]

 

[Aladin Akyurek]

replace gardia with a cell which houses a product of interest. did the formula work for gardia?

 

[Med4040]

It works when I added the formula in the first table

Now I put the formula in another tab. I tried to change the range but I am missing something.
In the second tab, the product range is E4:LL4. The first tab is called ProductMatrix



INDEX($E$4:$LL$4,MATCH(MAX(IF(ISNUMBER(SEARCH(B78,$B$2:$B$500)),ProductMatrix!$Q$2:$Q$500)),IF(ISNUMBER(SEARCH(B78,$B$2:$B$500)),ProductMatrix!$Q$2:$Q$500),0))

Did I miss something?

 

[Med4040]

Aladin, I got it now

It should be like this

INDEX(ProductMatrix!$C$2:$C$500,MATCH(MAX(IF(ISNUMBER(SEARCH(E1,ProductMatrix!$B$2:$B$500)),ProductMatrix!$Q$2:$Q$500)),IF(ISNUMBER(SEARCH(E1,ProductMatrix!$B$2:$B$500)),ProductMatrix!$Q$2:$Q$500),0))

Thank you so much for your help

 

[Aladin Akyurek]

@ Med4040

You are welcome. Thanks for the update.