Can this be changed to VB code?

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Can someone help with converting this to VB code? I still
have a few more arguments to add to it and it's just
getting WAY too lengthy!

=IF(E5=LOOKUP(E5,DATA!$S$2),VLOOKUP(D7,DATA!
$A$3:$U$28,19,0),IF(E5=LOOKUP(E5,DATA!$Q$2),VLOOKUP
(D7,DATA!$A$3:$U$28,17,0),IF(E5=LOOKUP(E5,DATA!
$R$2),VLOOKUP(D7,DATA!$A$3:$U$28,18,0),IF(E5=LOOKUP
(E5,DATA!$T$2),VLOOKUP(D7,DATA!$A$3:$U$28,20,0))))*G7)

Thanks in advance!
 
Hi Tom,
why are you uing LOOKUP?. you are searching in just one cell. e.g.
E5=LOOKUP(E5,DATA!$S$2).
This could be replaced by E5=DATA!$S$2

Looking further at your formula you seem to use the column index of the
DATA! reference as column index for your VLOOKUP function. You may try
the following
=VLOOKUP(D7,DATA!$A$3:$U$28,MATCH(E5,DATA!$A$2:$U$2,0),0)*G7
you may add some error checking if E5 is not found in row 2 or D7 in
your lookup range but this should work

Frank
 
Before trying to program anything, is there a reason why you use constructs
as :
IF(E5=LOOKUP(E5,DATA!$S$2), ..., ... )

Wouldn't IF(E5=DATA!$S$2, ... , ... ) be simpler (and at the same time avoid
possible #N/A 's. ?

Maybe I'm overlooking something, but this puzzles me.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top