Excel 2010 using vLookup, how to set formula for approximate values?

Question

Okay, so i'm trying to make a 'search engine' in excel. I have a huge list of names for lodging and i'm trying to make a formula where you type in their name and the vlookup will reveal that name's lodging information in the designated cell.

I have it functioning with EXACT values...meaning if I type the name EXACTLY as it looks in its cell on the left (i.e. column A), it will reveal the information in column B adjacent to the name in column A.  

But if I type in just part of the name, say for samuel i just put 'sam'.  The information won't show, it'll show #NA.  I tried using the 'true' function for the 'range_lookup' portion of the formula, but then it will find the highest/lowest (dont' know which oen) value in column B which obviously won't match the matching name.  

Is there a different formula I could use or anything I can do to make this easier?  I just want it to where I type something close to the name (like a search engine) and I can find that person's information with that approximate information.

Donut Tim · Accepted Answer

Try this:

In my Vlookup areas, I have the formula refer to a cell where I type in the name and I can change the name without altering the formula. In the formula instead of the reference to the cell with the name such as D1, type D1&"*". Set the formula to EXACT by adding False to it.

Remember: It will return info from the first name that matches. so if sorted alphabetically, and you only type "Mal: then of Mally and Malone, Mally will be selected.

You may wish to have another cell nearby that has a COUNTIF formula referring to your name entry cell to let you know if more than one match is present. Use the &"*" in that formula also.
Even better if your COUNTIF cell has conditional formatting to turn red if more than one match is present.
.

Trending News

Excel 2010 using vLookup, how to set formula for approximate values?

1 Answer