IF Function to vlookup a value to give % margin

[Gtasios4]

Hi All,

I need a formula to run in cell S4 and get the margin % 1-(buying cost/N4) ... Something like =IFERROR(IF(1-(VLOOKUP;A4;PRODUCT_INFO!$C$S;17;0)/N4);'')

But in my product info I also have another column (col. number 16) with a promo buying cost... so I need to run a logical test; if it finds a value in "promo buying cost" column in Product Info sheet, then run that division, if it doesn't find any value then take the "normal buying cost" (col. number 17) in Product Info sheet and then run the division.

 

[etaf]

Something like
=IFERROR(IF(VLOOKUP(A2,E2:H6,17,FALSE)=0,1-(VLOOKUP(A2,E2:H6,16,FALSE))/N4,1-(VLOOKUP(A2,E2:H6,17,FALSE))/n4),"")

add your vlookup formulas - as the one you posted did not seem correct

=IFERROR(IF(VLOOKUP(A9,E2:U6,17,FALSE)=0,1-(VLOOKUP(32,E2:U6,16,FALSE))/N4,1-(VLOOKUP(A9,E2:U6,17,FALSE))/N4),"")

Note the range must cover the 16/17 columns

I have added a very simple sheet example

Book1
	A	B	C	D	E	F	G	H
1
2	B	F2			A	F1		H1
3					B	F2		H2
4					C	F3		H3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IFERROR(IF(VLOOKUP(A2,E2:H6,3,FALSE)=0,VLOOKUP(A2,E2:H6,2,FALSE),VLOOKUP(A2,E2:H6,3,FALSE)),0)

 

[Gtasios4]

Something like
=IFERROR(IF(VLOOKUP(A2,E2:H6,17,FALSE)=0,1-(VLOOKUP(A2,E2:H6,16,FALSE))/N4,1-(VLOOKUP(A2,E2:H6,17,FALSE))/n4),"")

add your vlookup formulas - as the one you posted did not seem correct

=IFERROR(IF(VLOOKUP(A9,E2:U6,17,FALSE)=0,1-(VLOOKUP(32,E2:U6,16,FALSE))/N4,1-(VLOOKUP(A9,E2:U6,17,FALSE))/N4),"")

Note the range must cover the 16/17 columns

I have added a very simple sheet example

Book1
	A	B	C	D	E	F	G	H
1
2	B	F2			A	F1		H1
3					B	F2		H2
4					C	F3		H3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IFERROR(IF(VLOOKUP(A2,E2:H6,3,FALSE)=0,VLOOKUP(A2,E2:H6,2,FALSE),VLOOKUP(A2,E2:H6,3,FALSE)),0)

Works perfect!!! Thank you so much for your valuable help!

 

[etaf]

you are welcome