View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
Default VLOOKUP questions

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.


The idea is to strive for optimal performance which, of course, is
subject to the nature of the sheet layout.

I suspect the lookup uses UsedRange to establish ubounds. Note that
this may include empty cells beyond the data area. To determine if
extraneous cells exist, Ctrl+End should take you to the last data
row/col intersection. (Delete the extraneous rows/cols and save, then
Ctrl+End will locate properly)

Wait until you encounter a large table of data; -you'll notice it then.
Also, the Excel functions use C++ and so will always be fast[er] than
VBA.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion