Vlookup with "*" in the data field

[808stu]

I am using VLOOKUP (exact match)with a table that has the following values:

AGE	5
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!

 

[Qroozn]

I've had the same issue in the past.
try changing your VLOOKUP to a MATCH instead.

 

[AhoyNC]

Try putting a tilde (~) in front of the asterisk.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	AGE	5			AGE~*
2	AGE*	1		1
3	AGE*ACRE	0		1
4
Sheet

 

[808stu]

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]

You can use SUBSTITUTE in your formula to do it:

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

 

[808stu]

Thank you RoryA! It worked wonderfully!

 

[808stu]

Thank you to everyone again for lending your advice. It is much appreciated!