Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup Match on two columns

0

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?

excelformulas-functions
MS Office Version: Sroncey21
posted by: Sroncey21 (rep: 66) on Fri Apr 12, 2019 at 1:14 pm
Answer
Discuss

Answers

0
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")
posted by: Variatus (rep: 4889) on Sat Apr 13, 2019 at 12:11 am
Discuss

Discussion

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
Regards

Nik
Nik (rep: 2) 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: 4889) Apr 13, '19 at 10:37 am
Such great work! I appreciate you and the whole team. Thank you!!
Sroncey21 (rep: 66) 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