VLOOKUP with two criteria

Question!

I have two tables, Purchases and Sales:

Purchases:

SKU    SID    NID   Cost
001     A1     9A    $1.07
001     A2     4A    $1.07
002     A1     5B    $2.24

Sales:

SKU    SID    NID   Sale
001     A2              $10.99
001     A1              $9.99

So, I want to do a VLOOKUP in the "NID" column of Sales to get the "NID" from the Purchases sheet where the "SKU" and "SID" from the Purchases sheet match the "SKU" and "SID" in the Sales sheet.

I can match on one of the two. For example, I could do this to match on the SKU alone:

VLOOKUP(A2,Purchases!$A:$D,3,FALSE)

But, I'd need to also match on "SID" since there are different values for "NID" based on a combo of "SKU" and "SID." If there's a better option than VLOOKUP, that's fine too. Thanks!



Answers
=IF(AND(Sales!A3=Purchases!A3,Sales!B3=Purchases!B3),Purchases!C3,"NO MATCH")

-- I would let my answer there just in case you're not concerning about the comments below.

You can follow this practical tutorial. It does a work-around solution using Match and Index.

Regards

By : BrOSs


Vlookup is fine. Just make an additional column in both tables that's a combination of the 2 fields and do your Vlookup using that. You can call it "SKUSID":

SKUSID
001A1
001A2
002A1

(If the column is visually unappealling, you can also hide it from view. The calculation will still work.)

By : PowerUser


This video can help you solving your question :)
By: admin