View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Auric__ Auric__ is offline
external usenet poster
 
Posts: 538
Default VLOOKUP questions

I replaced this formula:

=IF(AE16="v",IF(AG16="w",-5.5,-0.5),IF(AE16="ns",-0.5,IF(OR(AE16="x",
AE16="$",AE16="hhv",AE16="cc"),-2.45,IF(AE16="app",0,""))))

....with this VLOOKUP:

=IF(AE16="?","",VLOOKUP(AE16,defaults!C:E,IF(AG16= "w",3,2)))

The lookup table is:

Type Fee WC
0 $0.00 $0.00
$ -$2.45 -$2.45
app $0.00 $0.00
cc -$2.45 -$2.45
hhv -$2.45 -$2.45
ns -$0.50 -$0.50
v -$0.50 -$5.50
x -$2.45 -$2.45

The problems are two-fold:

1) The VLOOKUP doesn't seem to be able to handle a literal "?", thus
necessitating the 1st IF(). Is there any way to include a "Type" of "?" in my
table?

2) The only difference between the table's "Fee" and "WC" columns is the "v"
value. This is handled by the 2nd IF(). Is there a better way to deal with
this?

--
There exists a limit to the force even the most powerful
may apply without destroying themselves.