I am attempting to check one list of roadways (each has a unique identifier) against a separate list. If that roadway on the first list is present on the second I then want to see if it also falls within the milepoints on the second list (for both lists I have a column with for the beginning milepoint and a column for the ending milepoint).
The formula (located in cell CC3) I have that is checking for the roadway on the second list is this:
=IF(ISNA(VLOOKUP('Sheet1'!CA3,'Sheet2'!Y:Y,1,FALSE)),"",VLOOKUP('Sheet1'!CA3,'Sheet2'!Y:Y,1,FALSE))
I am attempting to write a formula in another column that will check to see if the milepoints from the first sheet fall within the milepoints on the second sheet. Here is the formula for that:
=IF(AND(CC3<>"",F3>='Sheet2'!F2,G3<='Sheet2'!G2),"YES","")
Column F is the beginning milepoint and Column G is the ending milepoint on both sheets.
I realized that this formula isn't doing what I want since the second and third logic tests are simply looking at those respective cells on Sheet 2, not at the Column F & G values on the same row where it found the matching roadway unique identifier. How would I go about fixing this? I can seem to reason my way through it. Thanks for any help you can provide!
Bookmarks