Lookup Help
You can "replace" the asterisk with ~* by modifying your formula:
=VLOOKUP(SUBSTITUTE(A2,"*","~*"),sheet2!A:B,2,FALS E)
Saved from a previous post:
Excel supports wild cards (* and ?, any set of characters and any single
character).
The tilde is used to tell excel that you don't mean the wildcard--you actually
mean that character ~* and ~?.
Since tilde has a special purpose, you have to treat it special too: ~~.
And if your data has asterisks and question marks, you may want something like:
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~"," ~~"),"?","~?"),"*","~*"),
Sheet2!A:B,2,FALSE)
Tim879 wrote:
HI,
I'm trying to lookup (using vlookup) the string "*KwH Per Month". My
problem is that the * is not intended to be a wildcard character.
When I download the source report from SAP, it puts a * at the end to
signify that it is a total row.
I have tried using find / replace to replace * with Total and I got
the expected result of everything in the spreadsheet being replaced
with the word "Total".
I also tried the following formulas and both only return the first row
with KwH Per Month, not the *KwH Per Month row.
A4 contains the string KwH Per Month and Char (42) is ascii for *
=VLOOKUP(CHAR(42)&A4,'[ZMTIRR Summary.xls]Oct07 '!$A:$H,4,FALSE)
=VLOOKUP("*"&A4,'[ZMTIRR Summary.xls]Oct07 '!$A:$H,4,FALSE)
--
Dave Peterson
|