I have a list of IDs that I would like to select in a excel sheet. I am used to using the filter function to search for 1 ID, but now I would like to filter out all the rows with that match the ID in another columns of another sheet.

So for example, I have this reference list and other column to filter on:

REF:    column to filter sheet on:    output sheet desired has only these rows:
 a             a                            a  
 c             b                            c
 d             c                            d
 z             d                            z
 x             ...                          x
 y             z                            y

I am using the latest version of excel but I'm not used to using visual basic. However, any solution to this problem is highly appreciated.



Hope can be achieved by 'VLOOKUP' function. Please try the below steps and check your outcome.

Select a cell which is next to the column of REF. See the screenshot.

Go to Formulas > Insert Function > refer the screenshot and click on the “Lookup_Value” and choose the first column (“Sheet 1 column A”) > click on the “Table Array” and choose the column other sheet >Click on the “Col_index_num” and enter number ‘1’ > Click on the “Range_Lookup” and enter number ‘0’

enter image description here

Now you will have a value ‘#N/A’ in the selected cell which you have selected. Now copy ‘#N/A’ and paste it in the same column till the end of all the row in the first sheet. Insert the Filter option for all columns. Filter out all ‘#N/A’ and enter No in the next cells. Vice versa, filter out all values except ‘#N/A’ and enter Yes in the next cells. See the screenshot.

enter image description here

Now, filter out all Yes field to check the same ID's in both the columns.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.