match function to find name with comma

D

dmkent

New Member
Joined
May 21, 2009
Messages
17
I have a spreadsheet in which I use a match function to find the row a name is on. The name may be listed several times and the name has a comma in it, for example, "Tom, George". I got it to work once, but it doesn't always work. Is it because of the comma or because it is in the lookup several times? It's my understanding the Excel will return the row number for the first time it sees the name in the list, which is what I want.

Any ideas on why I get NA# with the name with the comma?

Many thanks for some help, especially since I got it to work once with the comma.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
MATCH(A2,ClientTrans!A$1:A$1004,0)

That's formula I'm using. It's really finicky. Sometimes it works and sometimes it doesn't - and I don't know why! At first I thought it was because the list wasn't in alphabetical order, but it works sometimes even in non-alpha order.
 
Upvote 0
MATCH(A2,ClientTrans!A$1:A$1004,0)

That's formula I'm using. It's really finicky. Sometimes it works and sometimes it doesn't - and I don't know why! At first I thought it was because the list wasn't in alphabetical order, but it works sometimes even in non-alpha order.

Bizarre - just tried it again and it works - any ideas on why this is intermittent? I spent 2 hours one day testing it different ways, and thought I had it fixed.
 
Upvote 0
The lookup array is something like this:

Tom, George
Zoe
April
Jack


And the i try looking up Tom, George and sometimes it doesn't work, and sometimes it does.
 
Upvote 0
dmkent,

Could there be a trailing space character in the cell that contains "Tom, George" in the sheet "ClientTrans", or in cell "A2"?
 
Last edited:
Upvote 0
Just deleted and started fresh. Not it doesn't work, even without a comma in any of the array. I feel like it has something to do with it being alphabetical?
 
Upvote 0
I may be the ultimate idiot! I had the wrong range in there and that's why it wasn't working. I have 2 pages names closely the same...hm, might change that!
 
Upvote 0
dmkent said:
The lookup array is something like this:

Tom, George
Zoe
April
Jack


And the i try looking up Tom, George and sometimes it doesn't work, and sometimes it does.
Click to expand...

Try one of...

=MATCH("**&A2&"*",ClientTrans!A$1:A$1004,0)

=ISNUMBER(MATCH("**&A2&"*",ClientTrans!A$1:A$1004,0))+0

which evaluates to either 1 = a match, or 0 = no match.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Return a user specified number of cell values to a comma list randomly
Replies
1
Views
76
Eric W
Eric W
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
356
USNA91
U
N
  • Solved
How to use the Substitute Function in VBA - I'm struggling to get code right
2
Replies
10
Views
694
Akuini
Akuini
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
131
still learning
S
I
  • Question
Lookup Data Across Several Sheets
Replies
1
Views
134
NickFru
N

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top