CODEWITHSUNDEEP
excelexcel-formula
Sharvari Johari
Sharvari Johari

Reputation: 1

How to look up multiple values at a time in excel?

I have a problem. I have multiple excel files all with a few numbers in a list. I need to take those numbers in those lists and see if those values are in another excel sheet with over 570000 values. Then mark the next column over with a yes or no. As it stands I'm looking up over a hundred thousand values individually using control F. I was wondering if it is possible to look up the whole list at one time. This problem could also be phrases as searching for multiple unique values in a sheet at one time.

Any help? Thank you!

Edit: So if I'm trying to find 4 values in a list of 1000 values, is there anyway to get the cell next to each of those 4 values to say "yes"? Or is that impossible? The 4 values in this case would be the lists from other excel files.

Upvotes: 0

Views: 2638

Answers (1)

user4039065
user4039065

Reputation:

A simple COUNTIF will show whether a value exists in another column no matter whether that column is on another worksheet or another workbook altogether.

Example: With both workbooks open, use this formula to find out if the value in A1 is in another workbook's (Other WB.xlsx) Sheet1 column B.

=IF(COUNTIF([Other WB.xlsx]Sheet1!$B:$B, A1), "Yes", "No")

If the two workbooks are open, you can Alt+Tab between to select column B from the other workbook while you are creating the formula. If you close the other workbook, the formula will reflect the full path of the closed workbook automatically like,

=IF(COUNTIF('C:\Users\user\Documents\[Other WB.xlsx]Sheet1'!$B:$B, A1), "Yes", "No")

You can fill the formula down quickly by simply double-clicking on the small black square drag handle in the lower right of the the cell.

Upvotes: 1

Related Questions