Search a list of names at one time in excelSolved/Closed

Question

Hi,
I have a huge list of names (list A) which I need to search with another list of names (list B) and see if any name in list A is in list B . Is it possible to enter all the names in list A and search it against the list of names in list B in one go? List A and list B are saved in separate excel sheet files
Thank you
Priya

Mazzaropi · Answer

Priya, Good morning. 
Suppose:
List_One.xlsx
Sheet1
A1:A1000 --> huge list of names
B1:B1000 --> FORMULA
List_Two.xlsx
Sheet1
A1:A200 --> List of names
Try to use:
List_One.xlsx
Sheet1
B1 --> =IF(ISNUMBER(MATCH(A1, [List_Two.xlsx]Sheet1!$A$1:$A$200, 0 )), "-ok-", "--- NOT FOUND ---")
Copy it down as necessary.
Is that what you want?
I hope it helps.
 --
Belo Horizonte, Brasil.
Marcílio Lobão

priya_mbial · Answer

I tried copying the formula in column 2 of list1, but still couldn't do the search.
I edited the formula and used it as follows:B1 --> =IF(ISNUMBER(MATCH(A1, [List2.xls]Sheet1!$A$1:$A$114, 0 )), "-ok-", "--- NOT FOUND ---")
Since the list has names should I use IF(ISNAME(MATCH(A1, [List2.xls]Sheet1!$A$1:$A$114, 0 )), "-ok-", "--- NOT FOUND ---")
Thank you
Priya

Mazzaropi · Answer

Priya, 
"...since the list has names should I use IF(ISNAME(MATCH(A..."
Absolutely not!
The answer of a MATCH function is a number (if found) or an error (if not found). The suggested formula is corret.
Take a look at these files I did as an example to you.
List1.xls --> https://www.sendspace.com/file/hcwy98
Lista2.xls --> https://www.sendspace.com/file/i212lt
Please verify if they solve your question.
I hope it helps.
 --
Belo Horizonte, Brasil.
Marcílio Lobão

priya_mbial · Answer

Hi Belo,
Yes you are right. Should I paste this formula in column B everytime to do the search. I tried editing the formula as B1 --> =IF(ISNUMBER(MATCH(A1:A4, [List2.xls]Sheet1!$A$1:$A$114, 0 )), "-ok-", "--- NOT FOUND ---") 
But couldn't perform an entire search in all the rows of list 1 against all the rows in list2. Is there  any way to do it?
Thank you
Priya

Mazzaropi · Answer

Priya, Good morning.
The suggested formula answers what you requested in your first message.
Now, your remark leaves me in doubt about what you really need.
"...But couldn't perform an entire search in all the rows of list 1 against all the rows in list2. Is there any way to do it? ..."
What do you mean by that?
You can have twenty thousand rows of names to check in worksheet1, against another forty thousand names in worksheet2.
You simply adapt the intervals in the formula for this.
Just copy the formula in column B of worksheet 1 to the last line of the existing name.
Remember, you have to have the individual answer for each name whether it exists or not.
If you had sent us your file the solution would have been in your hands for a long time.
Please save your Excel file to a free website, www.sendspace.com or ge.tt and place the download link here.
So it will be possible for us to help you more effectively. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

priya_mbial · Answer

Hi Belo,
Sorry for the confusion.  I can use the formula in column B of list1 and see if any name in list2 exists in list1.Thank you for the solution. 
I cannot understand the following solution: 
You simply adapt the intervals in the formula for this.
Just copy the formula in column B of worksheet 1 to the last line of the existing name. 

=IF(ISNUMBER(MATCH(A1:A4, [List2.xls]Sheet1!$A$1:$A$114, 0 )), "-ok-", "--- NOT FOUND ---"). Looking at rows A1 to A4 in list1, does that mean using intervals in the formula.
I have attached list1 and list2 files in the below link. Please find it
List1-https://www.sendspace.com/file/v0ecor
List2-https://www.sendspace.com/file/p8qkw5
Thanking you
Priya

Mazzaropi · Answer

Priya, Good afternoon.
Follow the links to your files with the suggested formula applied.
List1_Priya.xls --> https://www.sendspace.com/file/0awp8q
List2_Priya.xls --> https://www.sendspace.com/file/4p61bz
Take a look at them and tell us if they worked for you.
Understanding the correct syntax of functions is a very important step in order to take advantage of the total search capability that each function has.
See the link below the function MATCH:
https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a?ui=en-us&rs=en-us&ad=us
At List2_Priya.xls you have a lot of duplicated records.
The formula is wasting time conducting your research unnecessarily.
I hope it helps. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

priya_mbial · Answer

Hi Belo,
Good morning. Thank you for the link. Thank you for helping with the list.
Regards
Priya

Search a list of names at one time in excel

8 responses

Similar discussions

Subscribe To Our Newsletter!