Vlookup Match on two columns


Worksheet "Aged List" 

Tab "Data" 

Column D (Where I want Data matches - Yes or No) Range d2:d1429

I need to match Column A (Policy Number) & Column B (Worktype) on Data Tab and Match to Tab "Data2" Column A (Policy Number) & Column B (Worktype) 

Is there a formula to match based on both criteria?



Selected Answer

The formula below will return a count of the matches found.

=SUMPRODUCT(--(Table7[Policy Number]=$A2),--(Table7[Work Type]=$B2))

If no match is found the formula will return 0 and you can convert that to "No" simply by embedding the formula in an IF function. Obvioulsy, any count other than 0 would mean that at least one match was found that that should result in "Yes" by the logic you asked for. Therefore the formula below will do what you want.

=IF(SUMPRODUCT(--(Table7[Policy Number]=$A2),--(Table7[Work Type]=$B2)),"Yes","No")


Hi Can the same fomula be used if I wanted find the the number of busses in a fleet for a specific customer? The table is based on unique vin #'s as the key identifier and all I need to knwo is Joe Bloggs has 50 busses and George's bus Servcvice has 203 buses etc. The table has >20 000 entries based on the number of times the bus was repaired, fixed etc with multple vin#'s

Nik Apr 13, '19 at 8:33 am
I think that the formula is too specific to the OP's workbook to be directly transferrable to another one but the counting part of it could be used for the purpose you describe unless a simple COUNTIFS can be employed. That depends upon the exact arrangements in your workbook. I suggest you ask a question and attach your workbook. Note that I'll be off for the next 2 weeks but there are others here who will attend to you.
Variatus (rep: 2523) Apr 13, '19 at 10:37 am
Such great work! I appreciate you and the whole team. Thank you!!
Sroncey21 (rep: 46) Apr 14, '19 at 4:55 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login