CODEWITHSUNDEEP
excel
Umar.H
Umar.H

Reputation: 23099

Index Match to get a value before a date with a 2nd condition

consider the following data on sheet_x

Name, Date,    Area
Jon,  30/3/18,  1
Mo,   30/3/18,  1
Ti,   30/3/18,  2
Tai,  30/3/18,  2

on sheet_y i'll have a date in cell A2

30/03/18 for example

i'll use the following formula to get all the names before on a certain date

{INDEX(sheet_x!A2:A4,SMALL(IF(sheet_x!B2:B4=A2,ROW(sheet_x!B2:B4))mod((row()-row(a2)),11)))}

now this is great - it returns all the names that occur on that date when I drag this down, but I'm having trouble figuring out how to add a second condition in to the formula which only fetches all the names on the date for Area that i specify. would anyone know how to do this?

apologies if this is a badly formatted question - if I can phrase this better please let me know.

EDIT :

The reason I want to list this by date is to create a calendar of when a certain person is on Holiday but make the excel spreadsheet dynamic so the user can choose their Area.

so I have the following sheet with dates and I use the if formula to only select the the date in question. the mod formula, as I understand at mar 30 & mar 31 

    {INDEX(sheet_x!A2:A4,SMALL(IF(sheet_x!B2:B4=A2,ROW(sheet_x!B2:B4))mod((row()-row(**b2**)),11)))} 
# B2 = Mar 31 a2 = Mar 30


Mar 30, Mar 31, April 01,

Upvotes: 0

Views: 90

Answers (1)

JvdV
JvdV

Reputation: 75840

I think this would do the job

  • Enter a header in cell D1 p.e. Results

  • Enter this formula in cell D2 en confirm as array formula

    ={IFERROR(INDEX($A$2:$A$5,SMALL(IF(1=((--(Sheet_y!$A$1>$B$2:$B$5))*(--(Sheet_y!$B$1=$C$2:$C$5))),ROW($A$2:$A$5)-1,""),ROW()-1)),"")}

  • I'm assuming your second criteria (area) to be in Sheet_y!$B$1

  • Drag down the formula

Below my results with referenced cells for criteria 1 F1 and criteria2 F2: enter image description here

P.S.: I assumed (by your question's title) you wanted results before criteria1. If it must be the results on that specific date, you can just change the > for a =.

Upvotes: 1

Related Questions