FInd closest/nearest match in excel

Hi All
I'm using a formula to look up a person and find the location of that person in another tab =IF(B2=0,"",VLOOKUP(B2,CHOOSE({1,2},'EMP OFF'!E:E,'EMP OFF'!A:A),2,0)) this works fine where the name is entered correctly.
Where it fails, is that if I do not enter an exact match it returns N/A. What I would like is a formula in another cell, F4, to offer a close match or alternative nearest match, i.e. if I put in a name spelt slightly incorrectly (e.g. search for Joo Bloggs then the closest match should offer me Joe Bloggs) or it can offer a nearest match (e.g. can't find Joe Bloggs but found Bob Bloggs). The formula I'm using is =VLOOKUP(B2,Table135[[FORENAME]:[COMMENTS]],3,TRUE) but all it offers is a name seemingly picked at random and nowhere near the spelling of the name entered.

Can anyone help?

Hi Jeff

All names are sorted A to Z

I would settle for if could do that but if I misspell a name it returns a name which is totally different than was entered.

Please show an example with the following:

The name you use to search

The actual "closest" name in your data that you want it to match, and its row number
The two names before and two names after that name in your list

The name that it is actually returning, and its row number
The two names before and two names after that name in your list

It would be better to just attach the file but I'm guessing it has private data.

Hi

The results vary depending on the letter omission or change of letter in the name, it seems to go where it wills, It is not even close to a match. Only if correct, does it returns the correct row to the name.

I can't help without data.