View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
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