Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
Use the tilde to tell any function that can use wildcards that you actually
are looking for this particular string, as an example =VLOOKUP("~*KwH Per Month",Table,2,0) will look for the string "*KwH Per Month" -- Regards, Peo Sjoblom "Tim879" wrote in message oups.com... 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
Thanks a lot!!
As always, I appreciate your help On Nov 9, 10:56 am, Dave Peterson wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |