A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VLOOKUP questions



 
 
Thread Tools Display Modes
  #1  
Old March 2nd 17, 07:17 AM posted to microsoft.public.excel.worksheet.functions
Auric__
external usenet poster
 
Posts: 461
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.
Ads
  #2  
Old March 2nd 17, 08:12 AM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,448
Default VLOOKUP questions

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


Regards
Claus B.
--
Windows10
Office 2016
  #3  
Old March 2nd 17, 08:02 PM posted to microsoft.public.excel.worksheet.functions
Auric__
external usenet poster
 
Posts: 461
Default VLOOKUP questions

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

--
How can a stereotype be a stereotype if no one knows about it?
  #4  
Old March 3rd 17, 06:32 AM posted to microsoft.public.excel.worksheet.functions
GS[_6_]
external usenet poster
 
Posts: 695
Default VLOOKUP questions

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

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!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5  
Old March 3rd 17, 11:13 AM posted to microsoft.public.excel.worksheet.functions
Auric__
external usenet poster
 
Posts: 461
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.

--
Ha! You're *funny*.
  #6  
Old March 3rd 17, 03:44 PM posted to microsoft.public.excel.worksheet.functions
GS[_6_]
external usenet poster
 
Posts: 695
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup questions Steve Excel Worksheet Functions 4 April 22nd 09 09:44 PM
Two vlookup questions D.Jessup Excel Worksheet Functions 5 October 3rd 08 01:38 PM
VLOOKUP questions Noncentz303 Excel Worksheet Functions 5 May 11th 07 06:38 PM
Vlookup and few more questions :) [email protected] Excel Worksheet Functions 9 October 3rd 06 12:19 AM
VLOOKUP - 2 Questions Kathy - Lovullo Excel Worksheet Functions 1 April 14th 06 08:22 PM


All times are GMT +1. The time now is 12:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.