You are here:

Excel/Conditional Lookup

Advertisement


Question
Hi Tom

I have 2 input values in cell G1, H1
G1 contains an Event No. and H1 contains an Event Date

Then I have a list of data in columns A,B,C,D

Column A has the Value I want to lookup
Column B has the Event No
Columns C & D are Dates "From" & "To"

I want to lookup the value in Column A when the Event No in cell G1 matches the Event No in Column B AND when the Event Date in cell H1 falls between the Date Interval defined by values in Columns C & D.  The date in Cell H1 could be equal to either the From date or the To Date or lie anywhere in between both of them.  If there is no match, then I'd default the result to 0.

I think its something to do with an Index Match but struggling to get it to work

Is this something you could help with please?

I'm looking for a formula result (non-array if possible)

Thanks in Advance

Bob

Answer
Bob,

This is written for data in rows 1 to 1000.  Change the 1000 to a bigger number to check more rows.   

=IFERROR(INDEX($A$1:$A$1000,SUMPRODUCT(ROW($A$1:$A$1000),--($B$1:$B$1000=$G1),--($C$1:$C$1000<=$H1),--($D$1:$D$1000>=$H$1)),1),0)

The formula worked for me.  It does NOT have to be array entered.

It does assume that there will only be one row that matches the conditions.  

--
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.