(1) VLOOKUP with a First Name and an Asterisk

Here you a list of names (First + Last) and you have to look up for the marks for the students just using the first name.

When you use a normal VLOOKUP then it will return an error #N/A which means the value is not there in the list which is absolutely right.

But, when you combine the lookup value with an asterisk, you get the students marks without any error.

…this is how this formula works

In the above formula, you have used an asterisk after the first name which helps VLOOKUP to lookup for a value which starts with the first name you have mentioned and rest of the value can be anything.

(2) Combine VLOOKUP and an Asterisk to avoid Trailing Spaces

Now in this data, you just have first names but again you are not able to get the marks.

And the reason is, after removing the last names the in-between space is left there.

And now, when you trying to look up for the marks with the first name, you are again getting an error #N/A.

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.