Need a search function (or macro...)

[nameltrab]

Hi

I have a worksheet with 4 columns - 1 of which is employee name and one of which is employee number. I need to be able to search this list for specific names (the numbers function I can do.) The problem with searching names is we have more than 1 Bob, etc. Also, when using a name search criteria, the criteria needs to be exact or it won't find anything. For example, if the name in the list is Smith, Bob and I simply enter "Bob" - nothing is found...

=IF(ISNA(LOOKUP(B2,AODB!A3:D21)),"",(LOOKUP(B2,AODB!A3:D21)))

=(IF(ISNA(LOOKUP(B2,AODB!D1:D40,AODB!A1:A40)),"YAY !!",(LOOKUP(B2,AODB!D1:D40,AODB!A1:A40))))

=IF(ISNA(MATCH(B2,AODB!A1:A21,0)),"",(MATCH(B2,AODB!A1:A21,0)))

Above are some of the things I've tried with the aforementioned problems... I'm by far an expert, and know nothing of macros - but I am fairly literate !

Any suggestions would be great !!

Thanks -

Eric

 

[mvptomlinson]

Hi Eric,

You say you need to search the list for specific names, but what do you want returned? Or do you just need to know if at least one match is found?

Also, if you're seeking specific names, why would you search for Bob, and not search specifically for "Smith, Bob". Searching for Bob - whether you're trying to return a value or just check for a match - would be somewhat useless.

 

[nameltrab]

Thanks for your questions!

The data I'm looking for would be to input the employee name to return the employee number. On my sheet, the first column is the employee number, and the last column is the employee name (and because of other formula's, that order cannot be changed.)

As for why not input the entire name, I suppose you are right, there is no reason I cannot enter the entire name. Perhaps because certain names are long and or have different, or awkward, spelling and its more accurate to search for Bob than Bobb Smithsnovitchson. If I enter Bob, I'm sure to get a result, but if I try to guess at the spelling of Smithnovitchson, or don't know Bobb has 2 b's, I'll never find the name. (This sheet is used by 50 plus people, so the more simple the data is to enter, the better...)

Thanks again,

Eric