Return Value based on Multiple Cirterion

[prashantmpk]

Hi,

Based on Agent Name and Followup Date, Return the earliest File# that needs to be processed first

regards
PM

 

[SirJB7]

Hi, prashantmpk!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, you could use this formula:
=SI.ERROR(INDICE(A2:A16;COINCIDIR(C18&"_"&C19;F2:F16&"_"&L2:L16;1));"no file to process") -----> in english: =IFERROR(INDEX(A2:A16,MATCH(C18&"_"&C19,F2:F16&"_"&L2:L16,1)),"no file to process")
but the approximated MATCH requires that the data set is sorted by the search criteria, in this case, column F and then column O; otherwise it won't work properly.

Regards!

EDITED (SirJB7)
Fixed column from O to L. Debraj(ex-Roy)'s catch.

 

[Debraj]

ha ha ha.. @SirJB7
Follow Up date is in "Column L" ..
I am sure.. you missed it.. OP.. is too smart..

 

[SirJB7]

@Debraj (ex-Roy)
Hi, buddy!
You're right... will be an earthquake today?
However I now realize that it can be solved with SUMPRODUCT, so I leave it to you. Good luck.
Regards!

 

[Somendra Misra]

@prashantmpk

What result are you expecting in your sample file?
And the followup date in cell C19 can be any date or any one date from column L?

Regards,

 

[Debraj]

Hi Prashant..

Can you please check if below is working for you..

Confirm the formula by pressing Ctrl + Shift + Enter, Not Just Enter..

=INDEX(A2:A16,MIN(IF((L2:L16>=C19)*(F2:F16=C18),ROW(L2:L16)+1-MIN(ROW(L2:L16)))))

 

[SirJB7]

@Debraj(ex-Roy)
Hi, buddy!
INDEX instead of SUMPRODUCT, fine. This last should work as column A is numeric.
Regards!