[Auric__]

GS wrote:

Claus Busch wrote:

Hi Auric,

Am Thu, 2 Mar 2017 07:17:27 -0000 (UTC) schrieb Auric__:

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)))

try:
=IFERROR(IF(AND(AE16="v",AG16="w"),-5.5,VLOOKUP(AE16,defaults!$C$1:
$E$20,2,0)),"")

I don't think I really like that. I was hoping that there would be
something simpler than what I already have. And that still doesn't
seem to be able to handle a literal "?" in the table.

Also, why limit the table to 20 rows? I used the entire columns to
plan for whatever future expansion may happen. (I doubt the table
will ever reach as many as 20 rows, but I see no reason to introduce
artificial limits.)

The literal "?" is a common wildcard character; -pad it with a space
character and the vlookup works! Better, though, to not use it as
lookup value.<g

Hmm... It hadn't occured to me that vlookup would use wildcards. The "?" is
just used as a placeholder, so I suppose I could replace it with something
else, if I must. (I use "?" to indicate "don't know what this one is yet".
It works for me and I've been doing it for a few years.)

A few seconds of experimenting suggests that "!", "#", "-", and a handful of
Unicode characters would work if I wrap the vlookup in a different iferror:

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

....but I like that even less than what I already have.

Also, you might want to make your lookup table a dynamic named range.
This will limit the search to only data rows rather than forcing it to
search entire columns!

I'm fairly sure that Excel is smart enough to ignore empty cells in the
table, and I *know* it's smart enough to not search outside the used range
(see also: xlCellTypeLastCell). I don't notice any lag or speed difference
between "C:E" and "C1:E20"... I'll probably keep using the entire columns.

--
Ha! You're *funny*.