Match on two conditions where concatenate doesn't work...

[Matt_au]

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

 

[Peter_SSs]

Matt

Welcome to the MrExcel board!

You may have to format column F as Date and you may require a modification to cope if no date meets the criteria - but let's see if this is the sort of thing you want.

Excel Workbook
	A	B	C	D	E	F
1	Dates	Units		Date of Interest	Units of Interest	Result
2	29/07/2007	3		01/08/2007	3	29/07/2007
3	29/07/2007			01/08/2007		01/08/2007
4	29/07/2007	4		02/08/2007	4	30/07/2007
5	29/07/2007	2
6	30/07/2007	4
7	30/07/2007	5
8	31/07/2007	5
9	01/08/2007	6
10	01/08/2007	5
11	01/08/2007
12	01/08/2007
13	02/08/2007	6
14	02/08/2007	3
15
Match

 

[Matt_au]

Brilliant, that was exactly what I was after!

Thanks