Lookup

[27aquaholic]

Hello All,

I'm looking for a non VBA formula to find the last date an entry was made with conditions.

Return the date when the last entry "A" was made if the amount is > 1.0 and Mr. Hunt was a Lead or Assistant. (not Staff)

Also for "B"

Return the date when the last entry "B" was made if the amount is > 1.0 and Mr. Hunt was a Lead or Assistant. (not Staff)


Thank you All.

[FDibbins]

Welcome to the forum

For future reference, please take a moment to read the forum rules and make your thread title something descriptive of your problem - not what you think the answer might be. (think google search terms?).
Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

Try this

	I	J
2	A	30-Sep-15
3	B	2-Oct-15

J2=MAX(IF(($C$2:$C$21=I2)*($E$2:$F$21="Hunt")*($D$2:$D$21>1),$B$2:$B$21,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Shareez Saleem]

Try this in cell H2:
=IF(AND(D2>1,OR(F2="Hunt",G2="Hunt")),B2,"")

[FDibbins]

Shareez, how will that "Return the date when the last entry "A" was made", as per the OP's request?

[FlameRetired]

Just another way (non-array entry) with the same lookup list in I2:I3 that Ford used. I reasoned that if Hunt is not Staff the data implies he must be Lead or Assistant.

=LOOKUP(2,1/(($C$2:$C$21=I2)*($G$2:$G$21<>"Hunt")*($D$2:$D$21>1)),$B$2:$B$21)

[Shareez Saleem]

FDibbins,
Sorry if I was wrong, I thought the OP wanted to return the date to column H if the count is above 1 and Hunt is either an Assistant or a Lead.

=IF(AND(D2>1,C2="A",OR(F2="Hunt",E2="Hunt")),B2,"")

[FDibbins]

Perhaps Im missing something, but I read that as wanting to find the latest date when those conditions are met

[FlameRetired]

Perhaps Im missing something, but I read that as wanting to find the latest date when those conditions are met

That's the way I read it, too.