0

I have to sheets within a workbook. On sheet 1 I there is a date that is the LOOKUP criteria(cell B9). I need to look in Sheet 2 and if the falls between the dates in Columns A and C...if so, then lookup the info in columns D thru K and populate the cells (E10 thru E16) on sheet 1.

Sheet 1

Sheet 2

I currently used the following formula in cell E10 in sheet 1...

=LOOKUP(2,1/((DATEVALUE($B$9)>='AUSSIE Wkly'!$A$3:$A$500)*(DATEVALUE($B$9)<='AUSSIE Wkly'!$C$3:$C$500))

But nothing comes up as you can see. Please help.

D Brown
  • 33
  • 7

1 Answers1

0

In your example, you want to retrieve values in row 4. In Sheet1, cells E10, E11 you would use

=INDEX(Sheet2!$D$3:$D$500,SUMPRODUCT((Sheet2!$A$3:$A$500<=Sheet1!B9)*(Sheet1!B9<=Sheet2!$C$3:$C$500)*(ROW(Sheet2!$A$3:$A$500)-ROW(Sheet2!$A$3))))

=INDEX(Sheet2!$E$3:$E$500,SUMPRODUCT((Sheet2!$A$3:$A$500<=Sheet1!B9)*(Sheet1!B9<=Sheet2!$C$3:$C$500)*(ROW(Sheet2!$A$3:$A$500)-ROW(Sheet2!$A$3))))

You could put together similar formulas for the other cells.

I take the opportunity to stress the power of SUMPRODUCT, quite often more flexible and comprehensive than: 1) Array formulas, 2) Lookups, 3) COUNTIFS, 4) SUMIFS.

I am leaving here for those interested a few links showing that.

  1. Better than COUNTIFS

    COUNTIFS with ISNUMBER and multiple criteria

    Have COUNTIFS ignore all blank cells (= empty cells and cells containing "")

  2. Multiple lookups

    Searching an excel with two search words (multiple lookup) using vba macro

    Multiple-column based lookups in Excel

    Finding rows with multiple values in the columns

  3. On two questions related to yours

    Excel: Counting how many rows fall within a time period

    How to calculate results and plot data depending on results of a specific column in excel?

(and you are welcome here, which shows I am actually "fond of" SUMPRODUCT).

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • I apologize...but you lost me. What I am trying to do is for me to use the date in B9 on sheet 1 LOOKUP in sheet 2 to see IF the date falls between the date in Column A and C, if so then return the value/content in column D,E, F and so on to display that data on Sheet 1 in cells E10 -E16 correspondingly. Sorry ..I'm lost lol. – D Brown Sep 11 '20 at 14:42
  • 1
    @DBrown - I had inverted the roles of Sheet1 and Sheet2. I have now fixed that. At any rate, I guess the idea is clear. – sancho.s ReinstateMonicaCellio Sep 11 '20 at 18:13