Home 
Search 
Today's Posts 
#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. 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Vlookup questions  Excel Worksheet Functions  
Two vlookup questions  Excel Worksheet Functions  
VLOOKUP questions  Excel Worksheet Functions  
Vlookup and few more questions :)  Excel Worksheet Functions  
VLOOKUP  2 Questions  Excel Worksheet Functions 