Results based on multiple criteria

[klynne75]

I have 2 spreadsheets. 1st = Sales 2nd = Pricing. I need verify that the price I sold my products for matches the pricing spreadsheet.

Spreadsheet (Sales.Mar.15)

Location Code Ticket Date Document Date Customer Number ShipTo Price Item Number
UTAH 1/3/2015 2/15/2015 WABC0001 SLC $36.00 RM
UTAH 1/2/2015 2/15/2015 WABC0001 PROVO $48.00 PAV
WYOMING 3/1/2015 2/15/2015 WABC0001 ROCK SPRINGS $10.00 RM
WYOMING 8/1/2014 2/1/2015 WDEF0003 CORETTE $36.00 RM
IDAHO 2/15/2015 2/17/2015 WGHI0001 BOISE $80.00 F
UTAH 3/1/2015 2/28/2015 WGHI0002 SLC $90.00 C
NEVADA 2/20/2015 2/25/2015 WABC0001 PROVO $8.50 RM

Spreadsheet (Mar3QMS)

CustomerID(F) ShipTo(H) LocID(W) ProductID(X) FOBLocEffDate(Y) ItemPrice(AB) FOBLocExpireDate(P)
WABC0001 SLC UTAH RM 7/9/2015 $40.00 12/31/2015
WABC0001 PROVO UTAH PAV 3/22/2015 $64.50 12/31/2018
WABC0001 PROVO UTAH RM 3/22/2015 $64.50 12/31/2018
WABC0001 DUNPHY NEVADA F
WDEF0003 CORETTE WYOMING RM 7/9/2015 $20.00 12/31/2018
WDEF0003 ROCKSPRING WYOMING RM 11/21/2015 $62.00 12/31/2015
WGHI0001 BOISE IDAHO F 7/9/2015 $30.00 12/31/2018
WGHI0001 SLC UTAH C
WGHI0002 BILLINGS MONTANA C 7/9/2015 $30.00 12/31/2018


I've tried: INDEX(Mar3QMS!$AB$4:$AB$1248,MATCH(Sales.Mar.15!V5,Mar3QMS!$W$4:$W$1248&Mar3QMS!$F$4:$F$1248&Mar3QMS!$X$4:$X$1248&Mar3QMS!$H$4:$H$1248,0),0)

Sales.Mar.15!V5 = Location&Customer&Item&ShipTo

My sample spreadsheets aren't in the right order...but the gist is the same. My formula isn't working already and now I'm being asked to add the criteria that the ticket date (1st spreadsheet) must be between the 2 dates on the 2nd spreadsheet.

Help!

 

[lexusap]

Hi,

Can you attached workbook related to this issue? will be easier to understand.

Thanks
Lex

 

[Martin2404]

You could try something like this..

=INDEX(Mar3QMS!AB:AB,(MATCH(A3&D3&G3&E3,Mar3QMS!W:W&Mar3QMS!F:F&Mar3QMS!X:X&Mar3QMS!H:H,0)))
Enter formula with Cntrl, Shift and Enter


A3&D3&G3&E3 are the Location&Customer&Item&ShipTo which are compared to the similar colums in Mar3QMS using MATCH