I am trying to write a formula that will find the

cost of ingredients by a common unit value

from a list of units that have different values.

For example:

Kilo = 1000 units

Doz = 12 units

So, if cell 1 equals "kilo" then the cost price

(cell 2) is divided by 1000, but if cell 1 equals

"doz" then it is divided by 12.

I have these values ( kilo = 1000, doz = 12)

referenced on a different sheet and cell 1 is a

pick list.I have tried vlookup and match etc but can't

seem to get the formula right.

Hi, The Help for VLOOKUP() says:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value is the value to be found in the first column of the array.

Your lookup value is the value in 'cell 1'

and you already have a table_array which links the possible text 'values' such as doz to numeric values.So the result of VLOOKUP() is the number to divide by.

Hope this points you in the right direction.

Regards

PS check the order of the items in your table_array, and look at VLOOKUP() Help for the range_lookup parameter.

Thanks, it works now.

Ask Your Question

Weekly Poll