PDA

View Full Version : Multiple Criteria Lookup



jsabo
02-26-2013, 11:25 AM
I am trying to find a script or formula to perform a multi-criteria search with the following three criteria:

1. invoice number
2. Transaction number contains Y4 or YK (indicated in the transaction number and the leftmost column)
3. For multiple results, select the status associated with the latest date

So, in other words, if we search for Y4 or YK associated with a certain invoice number, it may return multiple results. In the case that it does, we need the script/formula to return the status associated with the result that occurred latest. In the case of the attached, searching for a certain transaction number can result in multiple instances of the same invoice but with different statuses. We need to pull the status associated with the latest date.

Excel file (http://www.excelforum.com/attachment.php?attachmentid=216827&d=1361902541)

Snapshot (http://www.excelforum.com/attachment.php?attachmentid=216828&d=1361902604)

Please let me know if I can clarify this further.

werafa
02-27-2013, 02:48 AM
You can use the find (excel) or Instr(VBA) to tell if a string contains another string. e.g. if instr(string 1, string2) > 0 then test = true

you can compare 2 dates, e.g. if date1 > date2 then test = true

you can copy dates if test = true, then use max(datefield) to find the latest date

or you can use this logic in VBA.

does this help?