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) |
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) |
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 |
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 |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com