Forum Discussion
Vlookup with '*' wildcard character
Hello Experts ,
Could you please help me with trick or method to use v-lookup on 'wildcard' character.
e.g. 7K_*1 should return '3' using v-lookup.
| 7K_11 | 1 |
| 7K_12 | 2 |
| 7K_*1 | 3 |
| 7K_*2 | 4 |
Regards,
Rajiv
Hey Rajiv-
Maybe try using this formula:
=VLOOKUP(SUBSTITUTE(E3,"*","~*"),$A$3:$B$12,2,FALSE)
I've attached a sample file for reference...
9 Replies
Hey Rajiv-
Maybe try using this formula:
=VLOOKUP(SUBSTITUTE(E3,"*","~*"),$A$3:$B$12,2,FALSE)
I've attached a sample file for reference...
Mr. Mickle
good day!
why substitute with "~*" ?
many thanks
- Excel interprets the asterisk is a wildcard. In order to interpret it as a literal you need to put a tilde in front of it.
The same behavior is true for Find and Replace. If you want to replace a literal asterisk, you have to used the tilde symbol.
try: Vlookup(A2,sheet1!(range),2,TRUE)
the true will search for 'almost match'
while FALSE will find EXACT match..
HTHtry sorting the list -- the answer will be correct
this time I think it shoud be set to FALSE
HTH
