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
This is written for data in rows 1 to 1000. Change the 1000 to a bigger number to check more rows.
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.
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