Need formula involving multiple Vlookup and/or multiple if situation

Note Data as follows:-

Product Code
1001
1002
1003
1004
1005

COUNTRY OF EXPORT
Group "A"
Saudi
UAE
Oman
Qatar
Bahrain

Group "B"
USA
UK
France
Germany
Spain

Group "C"
Kenya
Nigeria
S.Africa
Sudan
Senegal

COMMISSION DUE FOR EXPORT TO COUNTRIES BELONGING TO GROUP
P.C.--- "A"--- "B"---- "C"
1001---5%----7%-----3%
1002---3%----5%-----2%
1003---5%----5%-----5%
1004---6%----8%-----4%
1005---3%----5%-----2%

Information of each sale
Product Code : _____________ (This will be selected from a Drop Down List)
Country of Export : _____________ (This also will be selected from a Drop Down List)
Rate of Commission: _____________ NEED FORMULA TO DISPLAY THIS INFORMATION IMMEDIATELY.

Eg.: If Product Code is 1004 and Country of Export is USA - Rate must display 8%
or If Product Code is 1001 and Country of Export is Bahrain - Rate must display 5%

(Above is only an short illustration - Actual Data involves hundreds of Products & more than 30 countries in each group)
Please advise best way to enter data and formula.

Upload a sample file.

Please share example excel book with expecting result for better understanding.

Thanks
Ankur

CommissionIndicatorDraft.xlsx

Sample File Attached

Hi there,
pls find my solution attached. It works in several steps. However, first of all I have adjusted the underlying data, compare E1:E9. This way my index(match()match()) formulae can run smoothly …
Doing so, it is an approach of nested index(match()match())looking at step1 of 2 and 2 of 2


Kind regards from Hamburg,

Henning


p.s.: if that was helpfull, please provide POSITIVE FEEDBACK

Thank You Very Much.
Excellent - Though I find it difficult to understand the formula (as I am not at all conversant with the Index & Match functions), but your solution does exactly what I wanted. Fantastic.
I only hope that I will be able to replicate this in my actual working file which is very large.
Keep up the good work and Thank You Once Again.

Dear WKMAHESH,

no wories, just follow the logic step by step. Just in case, send me a message and we'll figure things out together

Kind regards from Hamburg

Henning

Dear Mr. Henning;

I applied your formula to my actual table using the same logic.. it seems to be working.. but I am doing something wrong as the answers are incorrect... I cannot figure out what wrong I am doing.

I am attaching the actual file in which the data of just one item is shown, the actual data consists of many many items, and I can insert rows to insert the remaining data once this formula works correctly in this short version.

So please help by checking the formula and correcting it (please put a note down telling me what wrong I am doing)
I sincerely appreciate your help and thank you in advance for the same.

Kind regards from Mumbai, India

Mahesh WoolvaraEXCEL FORUM - ACTUAL EXAMPLE.xlsx

Dear WKMAHESH,

please find the file attached. In there I copied your sheet and corrected the formula. Your issue was in the mechanism how the formulae 1. index() and 2. match() have been put together.
To make that crystal clear, I have put in an example, sheet: index( match() match () ). In plain text it is just about coordinates or like the children’s game "battleships": Depending on 2 elements, one per each dimension, i.e. cell b21 and d19 the formula reads the corresponding figure of the “playground”, i.e. the index area. Looking at this, you will see the obvious: The arrays of the nested formulae need to be exactly set parallel, i.e. with the same length! You have missed a row in your formula!

This is it – thumb up .
In the sheet ‘correction!’ you’ll find my other comments.

All the best and just in case, send me an email and we keep on working together


Regards from Hamburg,

Henning

p.s.: And now it is time for a super positive feedback isn’t it?! –> hit the * Add Reputation in the bottom left corner

Atention: I think the "calculation options" of my last attached example file are set to manual. --> hit F9 for calculations

Atention: I think the "calculation options" of my last attached example file are set to manual. --> hit F9 for calculations