Help with vlookup and using the result in the same formula

[JadeLu]

Hello

I have a spreadsheet that i have had to create for sales commissions. I have 13 different commission structures. I have used vlookup to find out what percentage of commission they should receive. My question is, can i use the result of vlookup (in the same cell) to get a $ figure instead of just the percentage?

[teylyn]

Hello,

In cell F7, use

=VLOOKUP('Melbourne Branch'!B7,'Commission Rates'!$A$3:$B$15,2,TRUE)*B7

copy down.

Your original formula did not have absolute addressing (the dollar signs) for the lookup range, so when copied down, the range in the formula changed. Also, you want to look up a value that is not an exact match, but falls within a range. Therefore, the last parameter of the Vlookup should be a "TRUE". You had a multiplication of two cells instead. It happened to produce the same result, but that was pure accident.

In any case, the Vlookup as above returns the percantag, which is then multiplied by the value in column B.

Format column F as $.

cheers,

[JadeLu]

Thank you very much, i have now worked it all out and have the formula working