How to use INDEX MATCH instead of VLOOKUP

Morning Everyone,

I consider myself to be good at Excel but I've always been a VLOOKUP guy because INDEX MATCH baffles and I can't seem to get my head around it. Today, however, I feel an INDEX MATCH is the best solution and I need help.

I am trying to create a basic calculator which looks at a few variables to determine what percentage gets applied.

Photography STR Calc.xlsx

In my calculator, there are 2 tabs; 1 - Data Capture, 2 - Rates

I'm trying to put a formula in cell E14 on the Data Capture tab which shows me what rate applies based on 2 variables.

• Variable 1 - Area Code in cell D10 on the Data Capture tab
• Variable 2 - Drop down option in cell C14 on the Data Capture tab

For instance, if there is an Area Code of 4 and All Equipment Worldwide is chosen, I want cell E14 to then show the result 2.530%

Can anyone give me a solution to this?

Once I can master it in this cell, I will carry the formula down to the rest.

Thanks

Photography STR Calc.xlsx

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

or this sumproduct... =SUMPRODUCT((Rates!$D$5:$J$5=C14)*(Rates!$C$6:$C$17=$D$10),Rates!$D$6:$J$17)
format as percent.

BTW, based on all equipment worldwide and area 3 it returns 2.332%, not 2.530%

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.