You are here:

Excel/MATCHing a date within a range of dates

Advertisement


Question
QUESTION: Tom, I hope I can explain this clearly.

I have a list of names in column B, amounts in column C, and dates in column D. Entries can be repeated in any column.

Then in column A, I concatenate each row.

I would like to MATCH name&amt&date in column A. HOWEVER.....the date can vary from 5 days before to 5 days after.

So my question is: how can I MATCH("DOG"&23.54&42385,$A$2:$A$100,0) so that it returns the location in A of an entry with DOG, 23.54, and the first date within 42380-42391?

SUMPRODUCT? Array formula? Something else?

I'm using Excel 2003, and want a formulaic solution. Not VBA.

Thanks for your help.

ANSWER: Jana,

so match returns the row of the match.  I am guessing you want the row.


=SMALL(IF(($B$1:$B$500="DOG")*($C$1:$C$500>=23.54)*($D$1:$D$500>=42380)*($D$1:$D$500<=42391),ROW($B$1:$B$500)),1)   entered with Ctrl+Shift+Enter rather than just enter since this is an array formula

should give you the first row where the conditions are met.   Also, it doesn't use the concatenated values in column A but goes against the original data.

Hope that works for you.

Note the hard coded values like "DOG" in the formula can be replaced with a cell address that points to a cell that holds the word "DOG" or whatever criteria you want to work with.

--
Regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: Oh, this is returning the row number instead of a MATCH value. Excellent! Just what I was looking for.

I don't want to hardcode a date in there, but simply use a reference instead. In that portion of your formula, may I:

($D$1:$D$500>=$D2-5)*($D$1:$D$500<=$D2+5)

If that will work, then it fills my needs perfectly.

Thanks, Tom!

ANSWER: Jana,

Yes,  ($D$1:$D$500>=$D2-5)*($D$1:$D$500<=$D2+5)  should work.   If you have any problems then do

($D$1:$D$500>=($D2-5))*($D$1:$D$500<=($D2+5))

but by order of precedence, the plus and minus sign should be evaluated before the comparison operators.

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: At first this formula resulted in a column of errors, Tom, but as I've been tweaking and playing with it for the past couple of hours, I now have many more values. But darn it, certain entries still result in VALUE or NUM errors no matter what I do.

I added in the criterion (IF($E$8:$E$744="A","B","A"))

I believe I learned at one point that certain functions or constructs don't work in array formulas. Will a nested IF like that work? (It's inside the main IF you gave me.)

Is there a list somewhere of the items that don't work in arrays? Gosh, I'd love to see it if so!

Thanks so much for your time.

Answer
I would have to see how you are using it, but all the other conditions produced a true or false condition which is converted in the formula to a 1 or zero.   The statement you show is returning an A or B string value so it seems inconsistent with how the formula was originally built.  

If you want to send a small sample workbook and tell me what you want to do, I can see if I can show the way.

twogilvy@msn.com

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.