Excel function help (1 Viewer)

I have a lookup list on sheet two set up to show the following drop down list on sheet 1:

0-3 months
3-6 months
6-9 months
9-12 months

On sheet 1 this list appears twice on each record, for example:

6-9 months
0-3 months

I need a formula that will look at the two cells on sheet 1 and enter the shortest time period and display it in a third cell on sheet 1, in this instance:

0-3 months

I have checked out IF functions and VLookup and I can't figure out how to do this as the contact is text and not numeric.

Can anyone point me in the right direction please and advise which function I should use, it its possible? I have searched high and low and have been unable to find anything similar.

Any help would be appreciated.

Will look at this more a little later on (about to go into a meeting) but as a starting point, how about stripping off the first character of each cell, converting it to a number, and comparing its value? e.g. 0, 3, 6, 9. That should result in you always getting the "lowest" string.

Hi Alc

Thanks for the response, could you be a bit more specific please?

Al

Hi Alc, ignore that last message.

You are a genius! I figured out what you meant and it works perfectly.

Thank you so much. Wow, easy when you know how.

For anyone else looking at this the formula I used is;

=IF(J11="0-3",1,IF(J11="3-6",2,IF(J11="6-9",3,IF(J11="9-12",4))))

Have a fabulous day

Al

Glad it helped. I was stuck running from meeting to meeting after I posted and totally forgot to get back to this until today.

THANKS for posting the answer. Be sure and mark your thread as SOLVED, this will help others.

Just a point, a nested IF can go 7 layes deep. Sometimes a requirement can grow beyond 7 so some alternatives might be necessary.
My preference is to create a lookup table on a seperate worksheet. The worksheet can be set to hidden to prevent the casual user from accidently changing the lookup table results.

This is an option (and encouragement) to expand your Excel Skills from your success with nested IF to the next level.
Here is a good site with three alternatives to the nested If statements:
http://new.office-watch.com/2009/excel-nested-ifs-and-alternatives/

Why would someone go to the trouble of using these more complex formulas with Look-Up tables?

The first would be the 7 level nesteing limit.
However, a Lookup table provides better documentation for version control.
In my case, I use Excel for all reporting and Data Mining of SQL Server data analysis. A SQL View might return 50,000 records where each column represents the return of a SQL funciton with one to a dozen parameters.

In Excel, the categories (metadata) that the nexted If statements provide bring meaning to the end user. Now imagine the nexted If statement analyzing 50,000 records times 10 columns. That can take a long time.
For larger data sets, speeding up the caculation ten to thirty times can offer faster response time plus easier Quality Assurance. A centralized lookup table vs every cell with a nexted If formula has its advantages.

Case of Lookup table being somewhat propritory:
If the lookup table needs to stay "more" hidden from the non-programmer Excel power user, make it vbVeryHidden - then it can only become visible using vba Code instead of the user interface.
Sheets("SheetName").Visible = xlVeryHidden"
Hids and can only be made visible again through the use of VBA code
Sheets("SheetName").Visible = True"

It might be worth knowing there was more than one way to accomplish the same result in case your success with this leads to more complex assignments.