Excel - Wildcard match with multiple criteria?

JLiiii

New Member
Joined
Jan 10, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
I have a database spreadsheet where I need to pull key information from the master data. As you can see in the below screenshots, the goal is to return the "Part Number" and the only data I can match with are the "Kenn" and the "Part Code", "Kenn" can be directly matched with "KENN No" from the master data and the part code is the first 6 digits of the "Part Number", so I am thinking of using the wildcard or LEFT function. I have tried the following formula but it did not work for me, please advise. Many thanks.

Main Sheet:
Kenn
Part Code
Front Door Strip Part No.
0828505
0099BQ
Formula goes here
0828510
0099BQ
Formula goes here
0828512
0099BQ
Formula goes here
0828514
0099BQ
Formula goes here
0828518
0099BQ
Formula goes here
0828521
0099BQ
Formula goes here
0828523
0099BQ
Formula goes here
0318530
0099BQ
Formula goes here


Master Data (Report_PC_Data):
KENN No
Part number
0318530
0285AL-51V
0318530
0285AM-1BV
0318530
0099BQ-1BV
0318532
0285AL-1DR
0318532
0285AM-V50
0318532
0099BQ-V50
0318534
0285AM-51V
0318534
0099BQ-4BH
0318534
0099BR-4BH
0318534
9456M-A
**Part Code = First 6 digits of "Part Number" in Master Data**

This example:
Match "0318530" with "0099BQ"
Return value = 0099BQ-1BV (Highlighted in the Master Data (Report_PC_Data) table)

I have tried the following formula (Does not work for me):
=INDEX(Report_PC_Data[Part number],MATCH("*"&[Part Code]&"*",IF([Kenn]=Report_PC_Data[KENN No],"*"&[Part Code]&"*"),1))
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel - Wildcard match with multiple criteria?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,148,396
Messages
5,746,447
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top