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

#### Matt_au

##### New Member
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEF
1DatesUnitsDate of InterestUnits of InterestResult
229/07/2007301/08/2007329/07/2007
329/07/200701/08/200701/08/2007
429/07/2007402/08/2007430/07/2007
529/07/20072
630/07/20074
730/07/20075
831/07/20075
901/08/20076
1001/08/20075
1101/08/2007
1201/08/2007
1302/08/20076
1402/08/20073
15
Match

#### Matt_au

##### New Member
Brilliant, that was exactly what I was after!

Thanks

Replies
7
Views
261
Replies
3
Views
156
Replies
30
Views
2K
Replies
1
Views
141
Replies
6
Views
3K

1,181,412
Messages
5,929,784
Members
436,692
Latest member
ModestMuse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back