Look up as per the more criteria

[kme]

1)- 1st Sheet Given Customer Sales Data . 2 type Of Market Are there . 1) Hyper 2) IR .
Hyper Market Have Rebate. Rebate % Table are Given 2nd Sheet . Based On Criteria.

2) Based On Criteria Rebate % Need to came "Sales Data" Sheet Colum "P" . Answer Need Through Formula.

3)Sample Answer Manually Typed In Colum "N" .

 

[kme]

Please Do . and also Kindly Provide Formula

 

[vletm]

kme​

Seems that there are some challenges to Do . and also Kindly Provide Formula
I offer something with 'my way'.
Anyway, there are/were some challenges:
# some ALL-texts has space too - I take care some of those
# some other text has space too ...
# are OTHER and OTHERS different?
# some Brands letters orders vary

I offer two ways to get P-column values
a) press [ Do It ] in Sales Data-sheet
>> You'll get values in P-column and values row from REBATE-sheet
>> You'll get a note if there are differences between Your N-column and P-column as well as You'll see - what makes differences.
b) press [ Do It ] in REBATE-sheet
>> same as above ... plus ... You'll see - how those value has solved? ... this takes longer time.

 

[kme]

Text Spaces are Rectified.
Based On The each Criteria Can Create each Excel Formula . Formula Can Place Sales Data Sheet From "P" Column Onwards .