Find the MONTH in a range when we're provided the entire date

[Habanero Time]

Someone threw us a curveball.

This formula was working perfectly

=IFERROR(LOOKUP(2,1/((OrderIDs=K28)*(IO_Months=E30)),ROW(IO_Months)),0)

Now we're told that instead of being given the text name of a month, we'll be given an entire numerical date but still have to extract the MONTH info.

How can the existing formula be modified?

NOTES
IO_Months is a named range on a raw data sheet
Cell E30 (on the main page) currently says "January"
Cell E31 says "February" ...

So, the IO_Months range will stop having "January" and will start showing, for example, "1/19/12"
I was looking for a way to nest the MONTH function in here and haven't figured it out.

Thanks for your help!

[adaws]

Use =text(E30,"mmmm") to get January assuming E30 has 1/19/12 and then use the same formula u were using changing the lookup to the new cell

[Habanero Time]

Use =text(E30,"mmmm") to get January assuming E30 has 1/19/12

It's the other way around

The raw data used to say January. Now it's going to say 1/19/12
Cell E30 is on the main worksheet and summarizes January activity. Cell E30 will still say January but the formula needs an extra step. It's got to go to the raw data and no longer look for "January"; it's got to look in the range and recognize 1/19/12 as being January.

[Habanero Time]

I've tried this and it's just results in an error:

=IFERROR(LOOKUP(2,1/((OrderIDs=K28)*(IO_Months=(MONTH(IO_Months)=1))),ROW(IO_Months)),0)

[Haseeb Avarakkan]

Hi, try any of this,

=IFERROR(LOOKUP(2,1/((OrderIDs=K28)*(TEXT(IO_Months,"ddd;;;")="Jan")),IO_Months),0)

Or with CTRL+SHIFT+ENTER rather than just ENTER

=MAX(IF(OrderIDs=K28,IO_Months))

If not can you please attach your dummy file with desired results? So the members can see your requirements.

[vlady]

Hello
i think your just getting the value of the month in a date

=MONTH(VALUE(E30))

[daddylonglegs]

adaws has the right idea. You can apply TEXT function to the named range IO_Months to pull out the month as text. Try this version


=IFERROR(LOOKUP(2,1/(OrderIDs=K28)/(TEXT(IO_Months,"mmmm")=E30),ROW(IO_Months)),0)

[Habanero Time]

adaws has the right idea. You can apply TEXT function to the named range IO_Months to pull out the month as text. Try this version


=IFERROR(LOOKUP(2,1/(OrderIDs=K28)/(TEXT(IO_Months,"mmmm")=E30),ROW(IO_Months)),0)

That did it! THANKS!