They say you learn something new every day.

I have a spreadsheet, with a list of days in it. I want to sort them by their order in the week (ie, Monday – Sunday), but Excel being Excel, it seems them as words and sorts them in alphabetical order.

I’ve had a bit of a play and found two functions I never really use, both of which can do this:

Match

=MATCH(A1,{“Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”,”Sunday”},0) 

This is neat and  concise, but just returns the array element number.

Lookup

=LOOKUP(A1,{“Friday”,5;”Monday”,1;”Saturday”,6;”Sunday”,7;”Thursday”,4;”Tues
day”,2;”Wednesday”,3}) 

 A bit longer, but allows you to return arbitrary values, not just the array position.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: