joeyslaptop

New Member
Joined
Aug 26, 2011
Messages
27
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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.
 
Upvote 0
maybe something like...



Excel 2012
ABCDEFGHIJ
1DataReturnedCriteria
2Report DateNameStatusReport DateNameStatusReport DateName
33/1/2015BillPending3/1/2015BillPending<>4/1/2015Bill
44/1/2015AnneApproved
53/1/2015AnnePending
64/1/2015BillDenied

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E3=DGET($A$2:$C$6,E2,$I$2:$J$3)
F3=DGET($A$2:$C$6,F2,$I$2:$J$3)
G3=DGET($A$2:$C$6,G2,$I$2:$J$3)

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top