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

Chillimonster · 20 Oct 2010 at 10:14

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

div0 · 20 Oct 2010 at 10:24

=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.

Chillimonster · 20 Oct 2010 at 10:32

Cheers - i'll go and give it a go.

div0 · 20 Oct 2010 at 10:44

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.

Chillimonster · 20 Oct 2010 at 10:48

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

Chillimonster · 20 Oct 2010 at 10:50

div0 said:
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!

Chillimonster · 20 Oct 2010 at 10:56

OK, seeing as i'm on a roll.

Is there any way to adjust the formula so that if no entries are in the fields it will not give the #N/A value in the results cell?

div0 · 20 Oct 2010 at 10:58

/edit - just saw your new updates...

Chillimonster · 20 Oct 2010 at 11:00

div0 said:
How are you creating the drop-down lists?

It seems to work ok for me.

All working as it should now, with the exception of the #N/A if there are blank cells being calculated.

div0 · 20 Oct 2010 at 11:06

Have a look here - http://www.ozgrid.com/Excel/stop-na-vlookup.htm

There's a few ways you can deal with it.

I'd do it the first way.

Say the formula I posted above is in E1, and that's the cell that shows N/A. Then in F1 put:

=IF(ISNA(E1),"",E1)

Then just hide column E and use F as your result.

Chillimonster · 20 Oct 2010 at 11:13

div0 said:
Have a look here - http://www.ozgrid.com/Excel/stop-na-vlookup.htm

There's a few ways you can deal with it.

I'd do it the first way.

Say the formula I posted above is in E1, and that's the cell that shows N/A. Then in F1 put:

=IF(ISNA(E1),"",E1)

Then just hide column E and use F as your result.

Works a treat.

Can't thank you enough.

Have a virtual beer on me

div0 · 20 Oct 2010 at 11:18

Glad you got it working. No probs.