HLookup

Hello everyone,

I don't know if I am on the right track here with HLOOKUP, I will explain what I am trying to do.. I have 2 worksheets, one is basically a template of a schedule (blank calendar), the other is a list of activities, with a code for the activity. What I am trying to do is have the schedule populate by searching the worksheet with all the activity information on it and return it to the blank schedule. The problem is this, I could have multiple of the same activities happening the same month so I also need to to search by DATE and let me know if there is a certain activity on a certain date and populate the schedule is anything like this even possible?

Ok, sorry about that, here's an example of what I'm trying to achieve. Basically when I enter a course code on sheet 2 under the date, I want it to look at the list of courses in sheet 1 as well as the date and let me know if this course is available on that specific date..

Here's a solution (works in Excel 2007+).

Here's an example (works in Excel 2007+).

Ok a little confused on =IF(SUMPRODUCT(--(sheet1!$B$85:$B$1590=A4),--(sheet1!$D$85:$D$1590<=A3),--(sheet1!$E$85:$E$1590>=A3)),"Available","Not Available") , when I paste that into the formula bar where do I enter the course code I'm looking for?

The formula shows a blank if there's no match.

Awesome, so bob, if I want to enter a different course code for a different date, do I have to manually go in and filter out the dates? Also - where do I enter the new course code, underneath the date or on the side like you did?

That works great!! Thank you! Ok, heres my next question, is it possible to have it display the Activity ID if it comes up "available"?

Well if there are multiple matches on the same day, this could be correct, sometimes we have more than one of the same course going.. for example one in the morning one in the evening... As long as it just scans within the date provided it's good!

Just enter the same format ("Course - " plus the ID#) in Column A (I already copied the formulas down through row 20). You can also change the dates if you like. Or, if you want to be able to specify different dates for different courses, use the attached.

I was just going to offer to do the same!

That works awesome as well! Thank you guys so much. Bob - is there a way to make it display the activity ID if it's "Available"?

Yes. But the approach is different using Example 1, where there's only one course you're looking for, versus Example 2 where you have multiple courses. So, which one do you want to use?

Also, what do you do about multiple matches? Do you only want the Activity # for the first match or do you need them all?

I will have to look at this this evening, if you can wait until tomorrow.

No problem. Thank you so much for your help Bob!

Actually, it was easier than I thought -- assuming you can live with my two helper columns in Sheet1. The first gives me a code to look up, the second simply duplicates the Activity # because lookups only look to the right and down (there are ways around this, but that adds even more complexity).

If you can live with that (you can hide those columns, shown in green, if you like), then my solution is on Sheet2. You can change the course number and dates, and it will return the Activity # for up to 7 courses that meet those criteria, if any. If there are no matches, it shows up as a blank.

Oops, sorry. One line got deleted in the previous file. Please use this one instead.

That looks amazing! So I can just transfer this over to the calendar format attached?

Also, is this something that will be easily to update? Once I learn about the formula I am sure I can play around with it a bit, where does the information in your "Lookup Code" column come from? I see it has multiple things in it at once..

The lookup code is just data from Sheet1 -- Column B plus a "." and then the date (in number format), followed by a "-" and a number that increments for each occurrence of the same course code and date. The "." and "-" are just separators to make it easier to check the values. This then provides a unique reference for the lookups in Sheet2.

This should be very easy to update assuming your basic formats don't change. Already, it's set up to look to entire columns, so you should be able to add data as needed and just copy the formulas down (make sure you copy ALL the formulas in the row).

In Sheet2, you can copy any of the boxed-in areas AS A BLOCK down as far as you need, and the formulas will adapt. Please be aware that I formatted the cells in the row immediately below the dates using white font. That makes the formulas there work but they appear to b invisble. I did that only for aesthetic purposes. The formulas there tell the other cells in that column how many occurrences of that course and date there are so you get them all (or at least up to seven). If you ever need to go beyond seven, you will need to look at formulas in a few rows up to figure out how it works.

If you ever decide to eliminate the "Course - " entry in Sheet 1 -- and just enter the course number -- that will require a number of changes, so maybe e-mail me if you need help on that. And let me know if you have other questions.

Regards.
bb

You are amazing. Thank you bob!