Lookup Help

[Tim879]

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)

 

[Peo Sjoblom]

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

 

[Dave Peterson]

You can "replace" the asterisk with ~* by modifying your formula:

=VLOOKUP(SUBSTITUTE(A2,"*","~*"),sheet2!A:B,2,FALSE)

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)

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)

 

[Tim879]

Thanks a lot!!


As always, I appreciate your help

You can "replace" the asterisk with ~* by modifying your formula:

=VLOOKUP(SUBSTITUTE(A2,"*","~*"),sheet2!A:B,2,FALSE)

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)

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)