To Array, Or Not To Array?

[SuperFerret]

I have a spreadsheet that is used every day, and it contains this formula:

=IF(A1="Pink",4,IF(A1="Black",5,IF(A1="Green",3,IF(A1="Blue",1,IF(A1="Yellow",7,8)))))

All this does is look at what word is chosen from a dropdown in a specified cell (for arguments sake A1) and returns a numeric value based on that

Pink - 4
Black - 5
Green - 3
Blue - 1
Yellow - 7
Otherwise default to 8

I've been trying to use array(?) but I haven't got a clue what I'm doing or whether this would actually be any quicker?

 

[RoryA]

I'd use a lookup table rather than hardcoding an array in the formula.

 

[SuperFerret]

Thanks for the reply Rory. Normally I would use a lookup table, but the values are never going to change.

The formula I provided has been in use on the spreadsheet for years and hasn't needed amending, we're just trying to tweak it now for efficiency

 

[SuperFerret]

I tried this (swiped from another thread) but it fails on Black, Blue and Blank

=LOOKUP(A1,{"Pink","Black","Green","Blue","Yellow",""},{4,5,3,1,7,8})

Do I need to add EXACT or something in there?

Blank I can solve by making sure White is selected instead but Blue/Black?

 

[Andrew Poulsom]

LOOKUP expects the lookup array to be sorted ascending.

 

[SuperFerret]

Ah... so more like this:

=LOOKUP(A1,{"Black","Blue","Green","Pink","White","Yellow"},{5,1,3,4,8,7})

But I'm still getting issues with Black and Blue?

Thanks Andrew I think I may be starting to get my head around this

 

[JackDanIce]

Dave,

I can't get this to work when there is no value in A1, but try:

=LOOKUP(A1,{"Black","Blue","Green","Pink","White","Yellow","";4,5,3,1,7,8,9})

Your wife

 

[SuperFerret]

Wife,

Like my toilet, you have fixed the block in my knowledge. Any luck on those pegs?

Dave

=LOOKUP(A1,{"Black","Blue","Green","Pink","White","Yellow","";5,1,3,4,8,7,8})

 

[JackDanIce]

No, there's been some revolt in the country and my suppliers say production's been put on hold. Like your toilet, I have fixed your plumbing!

 

[SuperFerret]

Darn! I need my pegs! I hope they get it sorted soon, I'm having to train bluetits to perch on my washing line to keep stuff on it at the moment