lookup with mulitiple validations?

[sixsteps268]

I've been searching for about an hour can't find an answer so I'm posting a new thread. I'd love any help you guys can provide me.

I am working on some analysis that compares sales to returns.

I have a workbook with a sales sheet that contains Order ID #'s and Sku #'s. Then I have a returns sheet in the same workbook that contains Order ID #'s and Sku #'s.

Some Order ID #'s are single SKU orders, some are not. I have many instances of multiple sku orders where only 1 SKU was returned and not the whole order.

A simple vlookup returns if the order shows up as being returned, but I can't seem to figure out how to return a value if both the order and the sku is returned.

I can't quite get the logic to work. It's like if the order is returned and if the sku of that same order is returned, then return the value.

Does that make sense?

 

[Sammi]

Are you trying to show whether each individual sku is returned?
If so I would make a helper column on both sheets that contains the order # and sku# joined together so =order&sku.
Then do a lookup against that.

 

[Redwolfx]

F5 and G5 Are the Order and Skew Numbers

A2:C19 Represent the information

Column A Order #s
Column B Skews
Column C Returned Values

You will probably need to modify the code to fit your info.

=INDEX(A2:C19,MATCH(F5&G5,A2:A19&B2:B19,0),3)

Confirm with Ctrl+Shift+Enter not just Enter

 

[sixsteps268]

Concatenate. Of course! Brilliant!

 

[aaronbens]

Are you trying to add a column in the Sales sheet that will tell you if that line was returned? If so they the following would work:

=IF((COUNTIFS(Returns!$A$2:$A$10,A2,Returns!$B$2:$B$10,B2))>0,"Yes","No")