Referencing a rate calculation in another cell

[Fish55]

I am currently creating a excel sheet which will be used to calculate the cost of an item by looking up a referencing code and applying a pre-written rate to it.

I have used the "IF" function to look up the reference code from a list and then I want it to use the rate I have entered in the cell next to it. The issue I am having is that if I click on the cell with the rate formula I get #VALUE!, I believe this is because the rate itself is made up of a calculation.

I have attached an example sheet to illustrate my query. In the example I am trying looking up column A in the data and then based on which item it falls under I want to multiple using the rate calculation noted.

If I manually enter the calculation into the formula it works but I want to be able to edit the one cell with the rate and then my formula will automatically update as there will be hundreds of lines.

Any help is appreciated,

[Ace_XL]

Excel reads those rates (in C10:C12) as text and not numbers

use the = sign before each of them, format the cell as 'general' so as to convert them to a calculated number.

Then in Cell c2 use
=$B2*INDEX($C$10:$C$12,MATCH($A2,$A$10:$A$12,0))

Copy down

[Fish55]

Thank you so much for the help!

It is working now.

[FlameRetired]

@ Fish55.

This thread is a duplicate of https://www.excelforum.com/excel-gen...ml#post5257632

Please read our forum rules regarding duplicate threads.

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.