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))





Excel Help! wrote:

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.

--

Dave Peterson

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?

"Dave Peterson" wrote:

=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))





Excel Help! wrote:

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.

--

Dave Peterson
.

Just add another =if() function:

=if(isnumber(match(...)),"yes","no")



Excel Help! wrote:

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?

"Dave Peterson" wrote:

=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))





Excel Help! wrote:

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.

--

Dave Peterson
.


--

Dave Peterson

Works great! Thanks a lot!

"Dave Peterson" wrote:

Just add another =if() function:

=if(isnumber(match(...)),"yes","no")



Excel Help! wrote:

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?

"Dave Peterson" wrote:

=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))





Excel Help! wrote:

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.

--

Dave Peterson
.


--

Dave Peterson
.

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.
--
HTH...

Jim Thomlinson


"Excel Help!" wrote:

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.