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. 


Thread Tools  Display Modes 
#1




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




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




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




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.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#5




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




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.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
Thread Tools  
Display Modes  


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 