Check if value exists in an array of cells, and based off of the result perform a calculation

[fd_1001]

Hello,

I am having difficulty with a formula. My level of excel is pretty intermediate in the fact that I know how to perform basic functions/formulas, but this is over my head a little. So help is greatly appreciated!

I have two worksheets. One is a reference table, and the other is where I need to perform a calculation based on the lookup values returned in the reference table.

This is the reference table:

And this is the Values table, where I will need to work out the CommissionRate.

The Commission Rate (CR) is calculated by adding Cost+Rate. However, I need to be able to work out based upon the Make of the car and Manufacturing code, which rate to apply. The rate to apply varies seeing as there are different costs/rates for different manufacturing codes within each continent.

For example, for 'Ford', 'US', We know 'Ford' is made in N.America from the Reference table. Based on this, we can check country code US, and apply the Cost+Rate of 3+4. Therefore my commission rate would be 7.

I have code like this: =IF(VLOOKUP(A2, Reference!$F$2:$I$13,1,FALSE), MATCH(B2, Reference!$C$17:$C$43,0)) to try and lookup values, but as I said I am relatively beginner/intermediate and I don't know where to take this. My formula does pretty much nothing seeing as I need to have variants of SUMIF, IFERROR functions etc...

I am looking to learn more about Vlookups, Hlookups, Index and Match functions. But I am really stuck.

Thanks!
FD

 

[Sandeep_]

Hey F1001,

I think this can be solved in more easy way if you correct your reference table, ex.for US, rates and cost will always be picked up from N.America rather from Europe or any other continent. Maintain your reference table longitudinally rather laterally.

Best Regards.

 

[ITWare2008]

Hi FD,

I will put here a link for an xlsx file with a sample data achieving your request.
Take a look and learn things - is this expression correct (sorry if not) - Anyway if you have further questions please fill free to ask.

us_ford.xlsx - Speedy Share - upload your files here

Vândalo.

 

[AlexandraT]

I had a try myself with this one. As Sandeep_ suggested, the reference tables need to be changed. An example is in Sheet1 in the link. These only contain some of the values in your reference tables so you would need to get all the information you have in a similar format. I also made them into tables (personal preference, you can use normal cell references instead of table references).
I wasn't entirely sure what you want to do with the cost and rate, but in Sheet3 I added a separate cell for Rate, another one for Cost, and another one where the 2 formulas from Cost and Rate are simply added (note the "+" in the formula in cell E2).
I believe this is what you need.

https://www.dropbox.com/s/8arg9czn7vaox22/Rate and Cost.xlsx?dl=0

Alex

 

[fd_1001]

Hey F1001,


I think this can be solved in more easy way if you correct your reference table, ex.for US, rates and cost will always be picked up from N.America rather from Europe or any other continent. Maintain your reference table longitudinally rather laterally.


Best Regards.

I had a try myself with this one. As Sandeep_ suggested, the reference tables need to be changed. An example is in Sheet1 in the link. These only contain some of the values in your reference tables so you would need to get all the information you have in a similar format. I also made them into tables (personal preference, you can use normal cell references instead of table references).
I wasn't entirely sure what you want to do with the cost and rate, but in Sheet3 I added a separate cell for Rate, another one for Cost, and another one where the 2 formulas from Cost and Rate are simply added (note the "+" in the formula in cell E2).
I believe this is what you need.


https://www.dropbox.com/s/8arg9czn7vaox22/Rate and Cost.xlsx?dl=0


Alex

Hi Guys,

Thanks for the responses! I was a little unclear perhaps with my example, the way the reference table is set up unfortunately is because the country code is derived from the manufacturing code. Each commission rate is based upon which country specific parts have been manufactured i.e. Audi could have 3 separate parts which come from different continents/countries e.g. Toyota can be made up of parts which were manufactured in GB, HK and CN. Whenever a file is uploaded to this spreadsheet the data changes, and next time we calculate the commission rate the cars may have been made of parts purchased from different countries again.

Also, it is not specifically country specific, it can be area specific e.g HK is Hong Kong, LN can be London etc... so in order to work out the calculation we must first find out where the parts are located i.e. Continent, and then we must map the cost and rate of that to the country code (derived from the manufacturing code)

I've inserted a more detailed example here:

Does this clarify things more? I am at Uni at the moment so unable to access those shared files via the firewall. Will have to check the examples when I return home this evening. However I really appreciate the help!

 

[AlexandraT]

When you get a chance to look at the file I uploaded, I think it will work regardless. As long as the reference tables stay the same, my formulas should work. If you additionally need to get the country code, insert a helper column to get these and use:

=LEFT(B2,2)

Alex