Search a list of names at one time in excel [Solved/Closed]

priya_mbial 5 Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen - Mar 7, 2017 at 08:35 AM - Latest reply: priya_mbial 5 Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen
- Mar 8, 2017 at 11:08 PM
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
See more 

8 replies

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 7, 2017 at 09:57 AM
0
Thank you
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 5 Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen - Mar 7, 2017 at 10:16 AM
0
Thank you
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 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 7, 2017 at 10:52 AM
0
Thank you
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 5 Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen - Updated by priya_mbial on 7/03/17 at 11:42 PM
0
Thank you
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 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 8, 2017 at 05:28 AM
0
Thank you
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 5 Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen - Mar 8, 2017 at 05:49 AM
0
Thank you
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 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 8, 2017 at 12:16 PM
0
Thank you
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.office.com/en-US/article/MATCH-function-E8DFFD45-C762-47D6-BF89-533F4A37673A

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 5 Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen - Mar 8, 2017 at 11:08 PM
0
Thank you
Hi Belo,
Good morning. Thank you for the link. Thank you for helping with the list.
Regards
Priya