Search a range of data to return a value (IF statement)

[M.Johnson]

Hi,

I am stuck on this, but if seems it should be simple?

I have data that has months in it that are spelled wrong and in different formats etc. I need to be able to search the range of data I have created in a separate table in order to return the correct abbreviation into my spreadsheet. See below:

Need to be able to search this range (A1:E12) and return the corresponding horizontal value in the last column (Jan, Feb, Mar)

January Janurary Jan-11 Jan Jan
February Februrary Feb-11 Feb Feb
March March Mar-11 Mar Mar
April April Apr-11 Apr Apr
May May May-11 May May
June June Jun-11 Jun Jun
July July Jul-11 Jul Jul
August August Aug-11 Aug Aug
September September Sep-11 Sep Sep
October October Oct-11 Oct Oct
November November Nov-11 Nov Nov
December Decmber Dec-11 Dec Dec

I am currently trying this, but its not working! =IF(F2="","",VLOOKUP(F2,Dates!$A$1:$E$12,5,TRUE))

Please help! Thanks!

 

[Jerry Sullivan]

I am stuck on this, but if seems it should be simple?
<snip>
I am currently trying this, but its not working! =IF(F2="","",VLOOKUP(F2,Dates!$A$1:$E$12,5,TRUE))

Welcome to the Board,

VLOOKUP looks for matches in the left-most column of your array only- so the approach you tried will only return the desired result if there is a match in Column A.

The formula below seems to work.
=INDEX($E$1:$E$12,MAX(IFERROR(MATCH(F2,$A$1:$A$12,0),0),IFERROR(MATCH(F2,$B$1:$B$12,0),0),IFERROR(MATCH(F2,$C$1:$C$12,0),0),IFERROR(MATCH(F2,$D$1:$D$12,0),0)))

It's not pretty, but I'm going to assume that after 24 hours without a reply...
1. It's not a simple problem.
2. My ugly formula is better than nothing.

Hopefully, an expert will see this poor attempt and show us a better way to do this with an array formula- I wasn't able to find a way to do that.

 

[M.Johnson]

Thanks! It did work, now I just have to understand what the heck its doing so I can use INDEX and MATCH again in the future!

Thanks for your help!

Michelle

 

[T. Valko]

Hi,

I am stuck on this, but if seems it should be simple?

I have data that has months in it that are spelled wrong and in different formats etc. I need to be able to search the range of data I have created in a separate table in order to return the correct abbreviation into my spreadsheet. See below:

Need to be able to search this range (A1:E12) and return the corresponding horizontal value in the last column (Jan, Feb, Mar)

January Janurary Jan-11 Jan Jan
February Februrary Feb-11 Feb Feb
March March Mar-11 Mar Mar
April April Apr-11 Apr Apr
May May May-11 May May
June June Jun-11 Jun Jun
July July Jul-11 Jul Jul
August August Aug-11 Aug Aug
September September Sep-11 Sep Sep
October October Oct-11 Oct Oct
November November Nov-11 Nov Nov
December Decmber Dec-11 Dec Dec

I am currently trying this, but its not working! =IF(F2="","",VLOOKUP(F2,Dates!$A$1:$E$12,5,TRUE))

Please help! Thanks!

What's in F2?

 

[M.Johnson]

F2 is the look up value, the original data I am doing the formula against!

 

[T. Valko]

F2 is the look up value, the original data I am doing the formula against!

I know it's the lookup value but what EXACTLY is the lookup value?

Is it a date? The month name? What?

 

[M.Johnson]

Its a date, and it can be in the form of any of the 4 different versions in the first 4 columns in the range.

January Janurary Jan-11 Jan Jan
February Februrary Feb-11 Feb Feb
March March Mar-11 Mar Mar
April April Apr-11 Apr Apr
May May May-11 May May
June June Jun-11 Jun Jun
July July Jul-11 Jul Jul
August August Aug-11 Aug Aug
September September Sep-11 Sep Sep
October October Oct-11 Oct Oct
November November Nov-11 Nov Nov
December Decmber Dec-11 Dec Dec

 

[T. Valko]

Its a date, and it can be in the form of any of the 4 different versions in the first 4 columns in the range.

January Janurary Jan-11 Jan Jan
February Februrary Feb-11 Feb Feb
March March Mar-11 Mar Mar
April April Apr-11 Apr Apr
May May May-11 May May
June June Jun-11 Jun Jun
July July Jul-11 Jul Jul
August August Aug-11 Aug Aug
September September Sep-11 Sep Sep
October October Oct-11 Oct Oct
November November Nov-11 Nov Nov
December Decmber Dec-11 Dec Dec

Ok, so I assume that F2 could be a date like 1/21/2011 or it could be the month name January or it could be Janurary or it could be Jan.

Try this:

=IF(COUNT(F2),TEXT(F2,"mmm"),LEFT(F2,3))

 

[M.Johnson]

Thanks!

That worked great! Can you talk me through what the formula is actually doing and how does it know what to return? Especially when it is in date form of 1/21/11?

 

[T. Valko]

Thanks!

That worked great! Can you talk me through what the formula is actually doing and how does it know what to return? Especially when it is in date form of 1/21/11?

When the cell contains a TEXT entry that is one of the month names then we simply need to return the first 3 letters of that month name:

January: the first 3 letters = Jan
Janurary: the first 3 letters = Jan
Jan: the first 3 letters = Jan

When the cell contains a date like 1/21/2011...

The TEXT function evaluates the date and returns the month name as a TEXT string based on the format code of mmm which means the short month name.

F2 = 1/21/2011

=TEXT(F2,mmm") = Jan
=TEXT(F2,mmmm") = January

You can see how these format codes work by:

Enter some date in cell F2
With the cell still selected, Right click>Format cells
On the Number tab, in the little box under Type:
Enter mmm or mmmm
OK

See how that changes the display of the date value?

We use the format codes mmm or mmmm in the TEXT function to return the month name as a TEXT string.