Excel : Search for data in an excel data file from another excel file having the list of items


I have two excel files. One file has a complete data. Another excel file has a list of items. I want to look for the items in data file, which are from the list in another file.


2 Replies
best response confirmed by Niraj1 (Visitor)


You need to test each record within the data file to determine whether it meets the conditions set by the list.

= FILTER(data, COUNTIFS(extract, complete))

where 'extract' is the list of records to return and 'complete' is the corresponding field in the source 'data'. 

Note: FILTER is an Excel 365 function.


If you don't have FILTER() function you may add helper column with COUNTIF as inside of @Peter Bartholomew formula and filter on this column excluding zeroes.