Vlookup with "*" in the data field

808stu

New Member
Joined
May 2, 2018
Messages
5
I am using VLOOKUP (exact match)with a table that has the following values:
AGE5
AGE*
AGE*ACRE

<tbody>
</tbody>

Because of the asterisk, when I use the VLOOKUP function, it displays results from AGE* as 5 rather than 0 or nothing. I am using the VLOOKUP exact match, but I think Excel is confused with the "*" in the lookup field because it acts as a wildcard. My formula is: " =VLOOKUP(C7,TABLE,2,FALSE). Any help would be much appreciated. Thank you!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Qroozn

Well-known Member
Joined
Mar 12, 2002
Messages
543
I've had the same issue in the past.
try changing your VLOOKUP to a MATCH instead.
 
Last edited:

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,612
Office Version
  1. 365
Platform
  1. Windows
Try putting a tilde (~) in front of the asterisk.
Excel Workbook
ABCDEFGHIJ
1AGE5AGE~*
2AGE*11
3AGE*ACRE01
4
Sheet
 

808stu

New Member
Joined
May 2, 2018
Messages
5
Sorry, I forgot to mention, the data with the asterisk is downloaded and I don't want to change the data. I've found adding any letter after "age" will resolve the issue, but there are hundreds of rows to make this change and many users will use this so I want to avoid making everyone from having to make these edits. Thank you
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You can use SUBSTITUTE in your formula to do it:

=VLOOKUP(SUBSTITUTE(C7,"*","~*"),TABLE,2,FALSE)
 

808stu

New Member
Joined
May 2, 2018
Messages
5
Thank you to everyone again for lending your advice. It is much appreciated!:)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,988
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top