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.

 

[AlKey]

Or this
=IFERROR(B2/LOOKUP(1,-SEARCH(D$2:D$4,A2),E$2:E$4),"")

 

[Aladin Akyurek]

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.

It's

LOOKUP(MAX(reference)+DELTA,reference)

where DELTA a small positive number such that MAX(reference) < MAX(reference)+DELTA.

Although this will yield the last numeric value from reference, it's not desirable on two counts:

[1] Slow because MAX examines every cell of reference.
[2] MAX is sensitive to the error values like #N/A in reference.

The big number

9.99999999999999E+307

is a limit value of Excel itself. It's quite improbable that this value will ever occur anywhere in reference. Thus there won't be any need to guess what would be a suitable number for a given reference whenever we are after the last numeric value. So there is no need for any of the zillions variations like 2^15, 99^99, 1E100, etc. etc.

[3] We know that LOOKUP and kindred functions ignore error values if they possibly can (they are designed that way).
[4] LOOKUP (and kindred functions with match-type TRUE or 1) appear to recruit (a form of) the binary search algorithm, which is very fast.

Here are some relevant links on the matter:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

Given 1 to 4, we have formulas like...

=LOOKUP(9.99999999999999E+307,A:A)

=IFERROR(B2/LOOKUP(9.99999999999999E+307,SEARCH($D$2:$D$4,A2),$E$2:$E$4),"")