Index and Match

[gupta425]

Hello,
Hope my message finds you well I am an amateur Excel user, and was seeking help on a problem which has been bothering me all day

I have two sheets(Titled List 1 and List 2) in my workbook.
I want to use the Index and match function for the model code column to fill out the discount percentage on the List 2 sheet.

Here is the challenge though.
In list 1, as you will notice some of the model codes are not complete and have wildcards (%,-,") in them . What I wish to accomplish is this: If a code is listed as 104-1006% in list 1, I want my formula to display the same discount percentage for all similar products on the List 2 sheet (Like 104-1006-006).

I believe a partial match needs to be used, but I'm not sure how to deploy it. Right now, I am simply aware of the basic index and match function. I have 7000 data points to run this for. Any help would be deeply appreciated.

Please find attached a copy of the sample data for your reference.
I would love it if someone could explain how to solve this problem with the right formula, and also provide the explanation to it.

Thank you!

 

[NARAYANK991]

Hi ,

Can you manually fill up the range E2:E100 with the correct outputs ?

Narayan

 

[bluesky63]

Hi gupta425

Done, you need to update lookup worksheet

 

[gupta425]

Hi ,

Can you manually fill up the range E2:E100 with the correct outputs ?

Narayan

Hi,
Thanks for your input. I could do that, but I have 7000 data points on my sheet, I had simply shared a sample of the entire data set here

 

[vletm]

gupta425
If 7000 data points
... then could You test this SAMPLE DEMO by pressing [ Do It ]-button
This tries to find the most nearest match ...

 

[NARAYANK991]

Thanks for your input. I could do that, but I have 7000 data points on my sheet, I had simply shared a sample of the entire data set here

Hi ,

That was not suggested as a solution.

That was suggested so that if you could fill in the correct results and again upload the workbook , it would help to clarify the logic and verify the correctness of any developed formula.

Narayan