Any Excel Wizards out there that are good with LOOKUP's

Associate
Joined
5 Sep 2008
Posts
870
Ive got this formula for working out risks for a project at work.

=LOOKUP(SUM(LOOKUP(A1,{"","0-5","6-10","11-20","20-50","50+"},{0,1,2,3,4,5}),LOOKUP(B1,{"","Negligible","Minor","Serious","Major","Fatality"},{0,1,2,3,4,5}), LOOKUP(C1,{"","Improbable","Possible","Occasional","Frequent","Certainty"},{0,1,2,3,4,5})),{0,9},{"Low","High"})

The idea is thaqt if the value of the three colums when multiplied together is less than nine then it would display 'low', higher than 9 would be 'high'

However i think the lookup is adding rather than multiplying.

What have i missed
 
=LOOKUP(PRODUCT(LOOKUP(A1,{"","0-5","6-10","11-20","20-50","50+"},{0,1,2,3,4,5}),LOOKUP(B1,{"","Negligible","Minor","Serious","Major","Fatality"},{0,1,2,3,4,5}), LOOKUP(C1,{"","Improbable","Possible","Occasional","Frequent","Certainty"},{0,1,2,3,4,5})),{0,9},{"Low","High"})

EDIT - Important. The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
 
Last edited:
So you'll need to rearrange like:

=LOOKUP(PRODUCT(LOOKUP(A1,{"","0-5","11-20","20-50","50+","6-10"},{0,1,3,4,5,2}),LOOKUP(B1,{"","Fatality","Major","Minor","Negligible","Serious"},{0,5,4,2,1,3}),LOOKUP(C1,{"","Certainty","Frequent","Improbable","Occasional","Possible"},{0,5,4,1,3,2})),{0,9},{"Low","High"})

Also note: If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.
 
That works.... kind of....

If i do this on a set of cells that i enter the numbers manually it does the business.

However the cells need to have a drop down lists to pick the values, but if i use the drop downs all i get is #N/A
 
So you'll need to rearrange like:

=LOOKUP(PRODUCT(LOOKUP(A1,{"","0-5","11-20","20-50","50+","6-10"},{0,1,3,4,5,2}),LOOKUP(B1,{"","Fatality","Major","Minor","Negligible","Serious"},{0,5,4,2,1,3}),LOOKUP(C1,{"","Certainty","Frequent","Improbable","Occasional","Possible"},{0,5,4,1,3,2})),{0,9},{"Low","High"})

Also note: If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.



Superb!

You're a lifesaver!
 
Back
Top Bottom