Search if cell contains one of many criteria then show ref # or ......

[room99oat]

	A	B	C	D	E
1	Item name	Total Weight	lb	Criteria	lb
2	Dog lover	40000	=b2/e4	Fish	100
3	So fishy	20000	=b3/e2	Cat	200
4	Love bird	30000	=b4	Dog	300
5	Super cat	50000	=b5/e3
6	Big elephant	1000	=b6
7	Small ant	3000	=b7

<tbody>
</tbody>

Hi, I need help for above tables.

How can I check if cells in column A contain criteria words in column E or not. And if it contains, I want the total weight on that cell divided by value of F column as shown on column C.

Thank you in advance.

 

[FDibbins]

Try this, copied down...
=IFERROR(B2*INDEX($E$2:$E$4,MATCH(LOOKUP(9.99999999999999E+307,SEARCH($D$2:$D$4,A2),$D$2:$D$4),$D$2:$D$4,0)),"")

 

[Tetra201]

=iferror(b2/index($e$2:$e$4,match(lookup(9.99999999999999e+307,search($d$2:$d$4,a2),$d$2:$d$4),$d$2:$d$4,0)),"")

 

[Eric W]

Here's an alternate version of the same formula:

=IFERROR(B2/LOOKUP(2^15,SEARCH($D$2:$D$4,A2),$E$2:$E$4),"")

 

[Tetra201]

Just a comment:

9.99999999999999E+307 and 2^15 are hugely excessive but this does not affect anything.
The number should be >= the maximal expected value of SEARCH(substring,string).
So, for most practical purposes a 100 would be just enough.

 

[FDibbins]

oops missed the / instead of * thanks for the correction

 

[Eric W]

9.99999999999999E+307 is BigNum, essentially the largest number Excel can handle. Some people use it just so that they don't have to worry about calculating the actual max, or so that they can tell just by looking at the formula that it's looking for the largest number. 100 could mean many things, not just "the biggest number".

I used a smaller equivalent. The maximum number of characters in a cell is 32767. 2^15 = 32768, so that will definitely be larger than any value returned from SEARCH. Again, 100 would probably work fine, but why take the chance? Some of this is just personal choice, but what if someone copies and modifies the formula for a different purpose, not knowing what the 100 really means? It might break and they wouldn't know why.

So yes, 100 would probably be fine for most practical purposes in this situation, but there are other considerations as well.

 

[room99oat]

I use Eric's formula (2^15) and it works for the example table. BUT when i applied the formula to my data it doesn't work.
I already double check and everything seems correct.

I have 40 criteria and more than 2000 item lists. Does it case-sensitive?

 

[room99oat]

It works now!! Thank you all. Both formula are working fine. Thanks again.

 

[FDibbins]

Happy to help