Excel - Wildcard match with multiple criteria?

[JLiiii]

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))

 

[Fluff]

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.