Report

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

Ask a question priya_mbial 5Posts Tuesday March 7, 2017Registration date March 8, 2017 Last seen - Last answered on Mar 8, 2017 at 11:08 PM by priya_mbial
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 
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
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
Leave a comment
Helpful
+0
plus moins
Hi Belo,
Good morning. Thank you for the link. Thank you for helping with the list.
Regards
Priya
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!