Returning a lookup value based on multiple criteria.

**ahunter488** · 06-09-2011, 12:31 AM

I need help finding a way to take the following information in excel and do a lookup (was trying to use index and match with if condition but was unsucessful)

The following line is the type of information I am trying to use to perform the lookup using the part number and s/n I am attempting to look up the shipment date from a list of shipments and choose the ship date prior to the return that is closest to the return date and if the ship date is not prior to the return date, return no value or false, etc. I also have many part numbers some with same s/n's and need to confirm part # and s/n match as well.
(In this example I am attempting to return the ship date of 1/12/2006 and need this done without having to reorder the list or have the result based on order alone. Any help appreciated. Thanks

Ret.Date Part # S/N
4/8/2011 SP-Intermittent or No Lock 03-29-2010 190085 CI-5230-100 1230

Part # S/n Ship Date
CI-5230-100 1230 11/1/2011
CI-5230-100 1230 9/23/2005
CI-5230-100 1230 1/12/2006

**teylyn** · 06-09-2011, 12:46 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**ahunter488** · 06-09-2011, 01:40 AM

See attached sample file, please

QUOTE=ahunter488;2541086]I need help finding a way to take the following information in excel and do a lookup (was trying to use index and match with if condition but was unsucessful)

The following line is the type of information I am trying to use to perform the lookup using the part number and s/n I am attempting to look up the shipment date from a list of shipments and choose the ship date prior to the return that is closest to the return date and if the ship date is not prior to the return date, return no value or false, etc. I also have many part numbers some with same s/n's and need to confirm part # and s/n match as well.
(In this example I am attempting to return the ship date of 1/12/2006 and need this done without having to reorder the list or have the result based on order alone. Any help appreciated. Thanks

Ret.Date Part # S/N
4/8/2011 SP-Intermittent or No Lock 03-29-2010 190085 CI-5230-100 1230

Part # S/n Ship Date
CI-5230-100 1230 11/1/2011
CI-5230-100 1230 9/23/2005
CI-5230-100 1230 1/12/2006[/QUOTE]

**Haseeb Avarakkan** · 06-09-2011, 01:55 AM

Hello, Try

H2, copy down.

=MAX(IF('Ship Data'!$B$2:$B$35=F2,IF('Ship Data'!$C$2:$C$35<=G2,'Ship Data'!$C$2:$C$35)))

Array Formula, with CONTROL+SHIFT+ENTER.