MATCHIF function?

[joeyslaptop]

I often need a MATCHIF function in Excel, but am unaware of if one exists. Here's what I'm trying to do:

Report Date;Name;Status
3/1/2015;Bill;Pending
4/1/2015;Anne;Approved
3/1/2015;Anne;Pending
4/1/2015;Bill;Denied

I want to only MATCH on April's data without sorting by date or needing a clunky array formula. IOW I need a formula to find Anne's Status in a way that will not consider anything that isn't 4/1/2015. I want the workbook to be formula driven (so no macros), and without having to place warnings on the worksheet to ensure that other users don't sort the data by columns B or C.

How can I accomplish this?

 

[Weazel]

Well, its an array but I don't think its too overly clunky...

=IFERROR(INDEX(C2:C5,MATCH(E1,IF(A2:A5<>"4/1/2015"+0,B2:B5),0)),"") Control Shift Enter

Where E1 is the Name you are looking up.

You could also use a cell reference for the data 'not' to include

 

[joeyslaptop]

Thanks for responding and for the formula. Array formulas usually start to bog down my workbooks - especially when I have to do it for thousands of rows. I may have to go with date-sorted data and VLOOKUP(INDIRECT(MATCH the first of the specified date and MATCH the last of the specified date)). I'm still interested if anyone else has another option.

 

[Weazel]

maybe something like...



Excel 2012[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Returned[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Criteria[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]Report Date[/TD]
[TD="bgcolor: #FFFF00"]Name[/TD]
[TD="bgcolor: #FFFF00"]Status[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Report Date[/TD]
[TD="bgcolor: #FFFF00"]Name[/TD]
[TD="bgcolor: #FFFF00"]Status[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Report Date[/TD]
[TD="bgcolor: #FFFF00"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD]Bill[/TD]
[TD]Pending[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/1/2015[/TD]
[TD]Bill[/TD]
[TD]Pending[/TD]
[TD="align: right"][/TD]
[TD]<>4/1/2015[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]4/1/2015[/TD]
[TD]Anne[/TD]
[TD]Approved[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD]Anne[/TD]
[TD]Pending[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]4/1/2015[/TD]
[TD]Bill[/TD]
[TD]Denied[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=DGET($A$2:$C$6,E2,$I$2:$J$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=DGET($A$2:$C$6,F2,$I$2:$J$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=DGET($A$2:$C$6,G2,$I$2:$J$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeyslaptop]

That's the first time I've seen the DGET function. It appears that I can only specify criteria as a range. Is there a way to do it like this?

=DGET($A$2:$C$6,F2,AND(ReportDate="4/1/2015",Name="Anne"))

I need to do a column of about 1000 of these MATCHIF-type formulas. I haven't been able to find a way to do this without specifying a range for the third part of the formula.

 

[Weazel]

not that I know of.
the criteria would need to include at least one of the column labels you are trying to return

could you perhaps put the criteria in a remote part of the sheet ?

 

[rayk8301]

how about using a vlookup function with an IF function.

=if(date=4/1/15,vlookup(e2,a:c,3,0),"")
e2 is where the value is
a = date
b = name
c = status

 

[Weazel]

maybe something like...?

=INDEX($C$3:$C$6,AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/(($A$3:$A$6<>"4/1/2015"+0)*($B$3:$B$6="Anne")),1))

if you have excel 2010 or later

 

[joeyslaptop]

What I currently have in place is report-date ordered data, a 0 match to catch the first instance of the dates and a -1 to catch the last instance in order to capture the range of that date, and then a vlookup that uses that row range (as explained in an earlier post). It works, but then the data set can't be alternatively sorted.

It it would just be nice to have a formula that would ignore the dates I don't care about. Thanks for your help. That deb formula would have been great if I could have specified the criteria in the actual formula.