VLOOKUP - Multiply by a percentage if appropriate

[Hoopsah]

Hi All,

Wonder if you can help.

I've attached a sample worksheet showing roughly what I'm trying to achieve - I'm completely stumped.

Column B just shows an item code.
Column C shows the quantity
Column D is the rate for the item
Column E is where the formula that I need help with is.

If (In column E) it looks up the table and there is a whole number then just multiply rate x quantity. BUT, if the rate is a percentage then multiply by the percent.

I can only get it to multiply the whole number and not differentiate.

I've added a column (F) to show the answer that it should be.

Thanks all

(P.S. I'll probably be posting this in VBA Express too as I'm really hoping to have an answer before my team meeting tomorrow - Thanks again)

Hoopsah

	Item	Qty	Rate	Formula used in Col D	Extn	Formula used in Col D	Answer required
	A.1	£ 100.00	0.07	VLOOKUP(C3,M:N,2,FALSE)	£ 7.00	D3*E3	£ 107.00					A.1	7%
	A.2	5	329.23	VLOOKUP(C4,M:N,2,FALSE)	£ 1,646.15	D4*E4						A.2	£ 329.23
	A.3	2	21.36	VLOOKUP(C5,M:N,2,FALSE)	£ 42.72	D5*E5						A.3	£ 21.36
	A.4	£ 230.00	0.20	VLOOKUP(C6,M:N,2,FALSE)	£ 46.00	D6*E6	£ 276.00					A.4	20%
	A.5	£ 150.00	0.10	VLOOKUP(C7,M:N,2,FALSE)	£ 15.00	D7*E7	£ 165.00					A.5	10%
	A.6	3	25	VLOOKUP(C8,M:N,2,FALSE)	£ 75.00	D8*E8						A.6	£ 25.00
	A.7	4	33	VLOOKUP(C9,M:N,2,FALSE)	£ 132.00	D9*E9						A.7	£ 33.00

 

[Hoopsah]

Not to worry everyone - Got ann answer on VBA Express - Formula in column D should be: =IF(D3<1,1+D3,D3)*C3

 

[etaf]

will you have a value less than 1 - as percents should all be less than 1 giving your example

so perhaps an IF with the vlookup()

=IF( VLOOKUP(C3,M:N,2,FALSE) < 1 , D3*VLOOKUP(C3,M:N,2,FALSE), C3*D3)

 

[Joe4]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you are using 365, you can make use of the new LET function, which can simplify/shorten the solution etaf posted, as you would only need to perform the VLOOKUP function once.

 

[RoryA]

Got ann answer on VBA Express

In future please provide links if you cross-post on other forums. Thanks.

 

[Joe4]

In future please provide links if you cross-post on other forums. Thanks.

Yes, I had already asked them to do that in a Private Message.

Hoopsah, please provide that link to this thread, and be sure to always do this if Cross Posting here in the future.
Failure to follow these rules may lead to disciplinary action.

 

[Hoopsah]

Sorry all - been a while since I've been on here.
Updated the version of Excel that I'm using in my profile (365) and the link to the question and answer on VBA Express is:

[SOLVED] VLOOKUP - Multiply by a percentage if appropriate

Hi All, Wonder if you can help. I've attached a sample worksheet showing roughly what I'm trying to achieve - I'm completely stumped. Column B just shows an item code. Column C shows the quantity Column D is the rate for the item Column E is where the formula that I need help with is.

www.vbaexpress.com