Hi,
To set this up, imagine a consecutive column of dates, dates may be repeated up to 6 times (list is sorted to same dates are together), and a second columns of unit numbers, alongside a date however the unit number entry may be blank.
I'm trying to use Match to find a date that is the largest less than or equal to a given date, AND where the unit number is equal to x (x between 1 to 100, or blank).
Now I have tried to concatenate the date and the unit number, which works when I have an exact match, but with my data most often I will be looking for the largest less than or equal to, and concatenating date and unit number (or the other way around) means the largest but less than check number is not equal to the largest but less than date for a given number.
Does anyone have any ideas how I might solve this problem?
Simply stated I want the largest and less than date for a given unit number, and the concatenating approach doesn't work as described above.
Thanks,
Matt
To set this up, imagine a consecutive column of dates, dates may be repeated up to 6 times (list is sorted to same dates are together), and a second columns of unit numbers, alongside a date however the unit number entry may be blank.
I'm trying to use Match to find a date that is the largest less than or equal to a given date, AND where the unit number is equal to x (x between 1 to 100, or blank).
Now I have tried to concatenate the date and the unit number, which works when I have an exact match, but with my data most often I will be looking for the largest less than or equal to, and concatenating date and unit number (or the other way around) means the largest but less than check number is not equal to the largest but less than date for a given number.
Does anyone have any ideas how I might solve this problem?
Simply stated I want the largest and less than date for a given unit number, and the concatenating approach doesn't work as described above.
Thanks,
Matt