Forum Discussion
Nested IF function with multiple cells for comparison
Hello,
Allow me to explain what I am trying to achieve. I have 2 worksheets of data with a common field of an ID #. In sheet 1, the ID # is in cells F2:F88. In sheet 2, the ID # is in cells F2:F48. I also have a start date and end date on each sheet. In sheet 1, the start date is in cells G2:G88 and the end date is in cells H2:H88. In sheet 2, the start date is in cells G2:G48 and the end date is in cells H2:H48. I am trying to write a formula that would tell me if the ID from sheet 1 matches the ID # from sheet, then perform the function of if the start date from sheet 2 is greater than or equal to the start date from sheet 1 and the end date is less than or equal to the end date from sheet 1, return Y, otherwise return N. I am unsure of how to write this. Essentially, I am trying to compare the see if the date range from sheet 2 is within the date range on sheet 1, if the ID # is an exact match. My end goal is to produce a list of ID's from sheet 1 that do not have a data match on sheet 2.
Thank you.
Hi Lori,
If your end goal is to produce a list of ID's from Sheet1 that do not have a data match on Sheet2, then you can depend only on the ID in the matching process, you don't have to check the start date and the end date of each record.
So you can use this formula in cell I2 of Sheet1 to find the records that aren't in Sheet2:
=IF(ISNUMBER(MATCH(F2,Sheet2!$F$2:$F$48,0)),"Y","N")
Please find the attached file.
- Thank you for your quick response. Will this work even if there is the possibility of multiple records per ID on sheet2? Allow me to explain my data a little more clearly. Sheet 1 contains Airline reservations by ID # and start / end date. Sheet 2 contains Hotel reservations by ID # with start / end date. I am trying to identify Airline reservations that do not have a hotel reservation. There is a possibility that there will be mutliple hotel reservations per ID that fall within the airline reservation date range. There is also the possibility that there could be a hotel reservation that falls outside of the airline date range. Those hotel reservations that fall outside of the date range don’t matter to me. I am just trying to find a way to identify Airfare by ID without a hotel, without having to manually look at thousands of rows of data. Hopefully that helps to clarify what I am looking at and what I am trying to achieve. Thank you for your help.
Hi,
No matter if there are multiple hotel reservations per ID, that formula will tell you that at least there is one hotel reservation for the ID or not!
It matches the first occurrence of the ID and ignores the rest!
If there are no hotel reservations for the ID, it will return the value of "N".
The good news that the hotel reservations that fall outside of the date range don’t matter to you, so you can depend on that formula that will take into account only the ID!
- I will try this and see if it works for me. Thank you so much for your help.
