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

Matt_au

New Member
Joined
Jul 29, 2007
Messages
3
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
Joined
May 28, 2005
Messages
57,321
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top