Matching and arriving at Discounts for Various Vendors

Hi All,

Hope you are doing well!...I am trying to match the vendor discounts from a reference file and calculate the net required discount....Following are the rules that I am trying to achieve..

With regard to the reference file :


For each vendor if the net discount in the reference tab is greater than or equal to the discount in the original tab then the required discount should be the net discount in the reference tab...

For each vendor if the net discount in the reference tab is less than the discount in the original tab then the required discount is the discount in the original tab....

Also for vendors that are not present in the reference tab the required discount is the discount present in the original tab...

Can you please help me with the formula to achieve the same....Appreciate your help!...

I have attached the excel workbook....Also I have highlighted the answer column(Required ) in the original tab to be yellow..

Thanks,
Arun

Use this formula in D2:

=IFERROR(IF(VLOOKUP(TRIM(A2),'Reference '!$A:$B,2,0)>C2,VLOOKUP(TRIM(A2),'Reference '!$A:$B,2,0),C2),C2)

then copy down. You have a space at the end of each name in column A, hence the need for the TRIM function.

Hope this helps.

Pete

Thanks Pete_UK....Really appreciate your help!...

You're welcome - thanks for the rep.

Here's a shorter version of the formula, which only involves one VLOOKUP:

=IFERROR(MAX(VLOOKUP(TRIM(A2),'Reference '!$A:$B,2,0),C2),C2)

Hope this helps.

Pete