vlookup or index help

[drewmiller]

I've searched the site for a similar solution, but can not find anything.

Basically I want to do this:

If any part of A3 contains the value in row "codes2", populate the corresponding value in row "month", and place that value in cell B3

A3= "2014_09_Drop2_Reminder"

Search in "codes2" for value (in this case it would be "09")

Find corresponding value in "month" (in this case it would be "SEP")

Place that value ("SEP") in cell B3

Thanks in advance!!

 

[Somendra Misra]

Hi,

Can you post a sample file.

Regards,

 

[drewmiller]

Sure thing.

 

[Somendra Misra]

Try below formula in B3 and copy down:

=VLOOKUP(MID(A3,FIND("_",A3)+1,2),$D$3:$E$14,2,0)

Regards,

 

[drewmiller]

Wow! I can't thank you enough! This worked perfectly.

 

[Somendra Misra]

You are Welcome!!!

Regards,

 

[Haseeb A]

If you are looking to return month, then you can use without a lookup table,

=TEXT(MID(A3,6,2)*28,"mmm")

 

[Ramesh Deo]

If you are looking to return month, then you can use without a lookup table,

=TEXT(MID(A3,6,2)*28,"mmm")

Hi,
Haseeb
could you please explain the method multiplying by 28, i think it convert into serial no but i am not sure. and any method for year and day ????

 

[Haseeb A]

Hello Ramesh,

In A1 enter: =ROW(A1)*28

and copy to A12. Then format as m/d/yyyy. Now you can see dates like, 1/28/1900;2/25/1900....12/1/1900 (If you are using 1904 date system, will be 1904 rather than 1900). Because excel store dates in serial numbers. first date is 1/1/1900 which is 1...

If we format all these dates as "mmm" will show just months.