subtracting with vlookup?

[noobnxcl]

if lic# in column a = lic # in column e, i would like to use the corresponding avg wt in column f to subtract column b.

would i have to use if and vlookup function? (i.e. =if(a=vlookup(....), f-c)??).

any help would be much appreciated.

[vandanavai]

Hello

Try this formula in Cell C2, C3 etc

Please Login or Register  to view this content.

[noobnxcl]

thanks, that seems to work. but i forgot to add in if column b is less than the avg wt will i want to obtain the difference. how can i add that in?

=if(and(b2<volookup(a2,e2:f67,2,false),if((match(a2,e2:e67,)),<>"#$NA",(vlookup(a2,a1:f200,6,0)-b2))

i keep getting invalid formula.

[noobnxcl]

sorry, lastly, how can i also get rid of #VALUE! that shows up?

[ratcat]

G'day

Does this help

Please Login or Register  to view this content.

Please double check the formula. Its getting late here. Ta.

[noobnxcl]

thank you all for your help. this is such a great site! wish i had found this site sooner.

[ratcat]

may i ask what =if(b2="",""....refers to?

This to get rid of the #VALUE because of the Vlookup and the Match formula are looking for a value when there is no value an error will return in the formula.

So when B2 is "" (blank) the formula stays blank

Cheers

[noobnxcl]

sorry, i thought it was solved but found out it wasn't.

ratcat, thx so much for the quick reply. the problem i am having now is that i only want to subtract column b if it is less than the avg wt in column f.

for example, c16 should be blank since b16 is greater than f2.

[Teethless mama]

sorry, i thought it was solved but found out it wasn't.

Ratcat, thx so much for the quick reply. The problem i am having now is that i only want to subtract column b if it is less than the avg wt in column f.

For example, c16 should be blank since b16 is greater than f2.

=if(countif(e:e,a2),if(vlookup(a2,e:f,2,)>b2,vlookup(a2,e:f,2,)-b2,""),"")

[noobnxcl]

thank you so much!