MATCH or VLOOKUP?

[melbox]

I have a formula that looks at both the date and the title of a course to determine what quarter that course is in. But, but b/c of the mass amount of courses, I'd like the formula to look at a chart on the second tab that lists all of the courses, as well as the date they are due and the quarter they're in. Right now a handful of classes are built into the formula, but if I were to add all of them it would get ridiculously long.

I'd also like to add to it by telling it to return any courses with a due date of 2013 or older to return the value "2013."


Here is the current formula that I'd like to replace with a VLOOKUP one (I think?), and does not take into account the chart on the second tab.... Any help you can provide would be greatly appreciated!

=IF((B3=DATE(2014,3,31))*ISNUMBER(MATCH(A3,{"Emergency Response Overview","Taking Care of Your Back","HAZCOM","Emergency Eyewash and Shower","Office Worker Safety","Manual Material Handling","Personal Protective Equipment"},0)),"1st (2014)")


Quarter Report.xlsx

[JBeaucaire]

You can list all your classes in a single column somewhere... the replace your inline array of class names with a reference to that column:

MATCH(A3, ClassList!$A:$A, 0)

[daffodil11]

I'm a big fan of LOOKUP, just because you can fill it up with arrays and still enter it as a non-array formula.

D2:

=LOOKUP(2,1/(('Quarterly Classes'!$G$2:$G$23='Training Detail Report'!A2)*('Quarterly Classes'!$I$2:$I$23='Training Detail Report'!B2)),'Quarterly Classes'!$E$2:$E$23)

and copy down

Did you want to take it a step further and match day and month only, so that the 2013 due dates match up against the 2014 quarters?

[melbox]

Thank you both! Both of those options worked like a charm!

Daffodil11, I don't think I need to worry about 2013, except for just populating Column D with "2013" for any dates that fall within that year. Is there a way to add that to this formula? I'm sure it's easier than I'm making it....

And just out of curiosity, if you happen to have a formula that looks at day and month only, I'm curious to see it if it's not too much trouble. I think I may use it elsewhere if not here.

[daffodil11]

We just have to add a few more layers to the LOOKUP, similar to what you already had.

Warning: things are going to get ugly! If you ever find yourself entering massive complex formulas that are hard to keep straight, you can hit Alt+Enter to jump down to the next line and then enter some spaces to keep things lined up and easy to manage. The spaces and line breaks have no effect on the formulas.


D2:

Please Login or Register  to view this content.

If you want to keep things extra pretty, you can even encapsulate the entire thing in IFERROR to create a standard error message.

Please Login or Register  to view this content.

And then we tie the previous year into it:

Please Login or Register  to view this content.

And now if B = last year, it will just show last year. Otherwise, it will perform the function.

[melbox]

My goodness...this will keep me busy...lol. Thank you SO much!!! I'll be playing with this for quite a while! =D

[newdoverman]

I used named ranges for Quarters and Classes on worksheet Quarterly Classes and used those names in a formula in D2 of Training Detail Report and copied down.

The formula for 'Training Detail Report'!D2

Please Login or Register  to view this content.

[daffodil11]

No problem. It's a lot of fun coming up with creative solutions. Let me know if you have any other questions.

If you're curious about the LOOKUP(2,1/xxxxxxxxxxxx) formula, I've written out a few explanations, one of which is here:

https://www.excelforum.com/showthread.php?t=998288

If you can wrap your head around that, it will open all kinds of doors including the versatility of SUMPRODUCT which functions in a very similar way.

[melbox]

Thanks, newdoverman! Can I ask a silly question? How did it know to look at the other tab? It doesn't take the date into account, but it's still a nice and simple formula I'll use in the future! Appreciate your help!

[melbox]

I could use those doors opened, so I'll do my best to wrap my head around it...lol. Thanks again! Already looking at the thread you referenced!

[melbox]

Last question for today. Daffodil, I can't figure out how to combine the formulas. Obviously not an Excel expert here.... (*Hanging head in mortification*)

[daffodil11]

There's nothing to combine, it was a progressive buildup.

The first formula was just the standard; stuff month and date checker.

The second formula was that + Error handling.

The third was all of the above + last year checking.

Just plug this into D2 and copy downwards:

Please Login or Register  to view this content.

If your actual ranges are longer, just increase them as needed.

I'm no expert either. The more stuff I learn, the more I feel like I don't know.

JBeaucaire has more Excel rocket power in his little finger than I have in my whole body. Seriously.

[melbox]

I see what I did wrong. I had used the third formula by itself but it returned "No Matching Record" for anything not in 2013 so I assumed I had to combine them. I just found where I went wrong though when I plugged this into another spreadsheet (different columns/numbers)-I'd forgotten to change one of the numbers. Thanks again-you're a great teacher and it works like a charm now!

[daffodil11]

Awesome!

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[newdoverman]

I nearly always take a look at all the other tabs in a workbook that is posted. This can give valuable information on how the current worksheet was developed and what the general aim of it is. Many times, it leads to a different train of thought than what I started out with.