Check if Sheet1 Column A & B match Sheet2 Column A & B

Morning all!!

Hopefully a quick one for you here

Sheet 1 - (Training)
Column A = Employee names
Column B = Dates

Sheet 2 - (Holiday)
Column A = Employee names
Column B = Dates

both sheets have exactly same format, no difference in any layout, around 500 names to work through - Date is formatted at dd-mmm

I'm trying to find the simplest way to check if the names on the Training sheet with the dates assigned to it have holiday booked on the holiday sheet for the same date.
I think i have sorted it but i'd like to know i have have over complicated it or even done it the right way

So far i have in Column C

=A1&" "&B1

then pasted values (done on both sheets) then in column D i have

=IF(ISERROR(VLOOKUP(C1,Holiday!C:C,1,FALSE)),"",VLOOKUP(C1,Holiday!C:C,1,FALSE))

That obviously takes away the date format and replaces it with a numerical string (For 18-Aug it shows me 42234)
Have i done to much to it, could i have done it easier?
I did want to try index/match but i've never used it before :D

Regards
Rich

Well, you could simplify that by doing this:

=IFERROR(VLOOKUP(C1,Holiday!C:C,1,FALSE),"")

You could also do something like this:

=IF(ISNUMBER(MATCH(C1,Holiday!C:C,0)),"Clash","Available")

I was wondering, though, on your Holiday sheet if an employee has booked a week's leave will that be entered as 5 individual days?

Hope this helps.

Pete

Pete - Thank-you.
Yes, each holiday is in this sheet as a single line, so 5 days holiday would show on separate rows.
Did i need to add the formula to merge A1 & B1? could i have got my required result just by using the names and dates?

I think that using the helper columns in both sheets is an easier solution. Without them, you would need to have an array* formula which does the concatenation for you, and array formulae are not very efficient for full-column references - thus, you would need to limit the range, and if you add more data then the ranges would have to be adjusted if they are not big enough. Here's an example of how a single formula would work (put it in F1 for comparison):

=IF(SUM(IF(ISNUMBER(MATCH(A1&" "&B1,Holiday!A$1:A$1000&" "&Holiday!B$1:B$1000,0)),1,0))>0,"Clash","Available")

* This needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Copy this down in the usual way(s).

You don't need to use the columns C for this, so you have the advantage of having a single formula. However, you can see that it is more complicated than the other approach, and so it will be more difficult to maintain in the future.

Hope this helps.

Pete

thats great stuff, thanks very much. rep sorted.

Glad to be able to help.

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

I'm not sure what you meant by "rep sorted", but it would be appreciated if you could click on the Add Reputation button below any posts that you have found to be helpful (not just in this thread).

Pete

adding the reputation is what i meant didnt initially mark it as solved as my browser froze, now working

Rep added

Okay, thanks for the rep, Rich. I've had a couple of glitches myself this morning while on the Forum.

Pete