Posted to microsoft.public.excel.worksheet.functions
|
|
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
|