VLOOKUP HELP

  • Thread starter Thread starter Excel Help!
  • Start date Start date
E

Excel Help!

I need VLOOKUP to search across worksheets where the names are not the same
(example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A)
DoeJK. Can I have VLOOKUP find each name across worksheet 2 and 3? Here's
what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").
Thanks for any help.
 
=vlookup() expects the table to be on a single sheet--not three different tables
on 3 different sheets.

But if you're just trying to determine what sheet that name is located on, you
could use three formulas like:
=isnumber(match(a2,sheet2!a:a,0))

But that will search for an exact match--just like your =vlookup() formula does.

You could parse the name into the string you want, but I have no idea how you'd
get that extra K to search the last sheet.

But depending on what you're looking for, there may be help.

If you could match on the last name "Doe", would that be ok. (This is a
terrible idea if you have lots of Smith's or Patel's!)

You can use a formula that includes a wild card like this:

=isnumber(match("Doe"&"*",sheet2!a:a,0))
But it'll find a match for "DoeJ", "DoeJK" and even "Doerayso,JK"

If you want to try, you can get just the last name using a formula that finds
the position of the first comma:

=SEARCH(",",A1&",")
Then subtract 1 to get the last name (without the comma):
=SEARCH(",",A1&",")-1

Then use that in the =match() portion (and add the wildcard character "*", too):
=isnumber(match(left(a1,search(",",a1&",")-1)&"*",sheet2!a:a,0))
 
You will have no luck until you clean up the source data. XL will not do
fuzzy matches well. You want exact matches for the names.
 
Dave,

This works great! One addtional question, the result is a True or False,
how do I get yes or no in place of T/F?
 
Back
Top