Which Excel Formula would be the quickest?

New Contributor

I have two sheets of data. One is a master list, another is one of specific user data i need. How can I make it so that the first master sheet will highlight each matching value to the specific sheet?  

3 Replies
Please provide more detailed information - what exactly do you want to match?

Basically, I have two sheets of user IDs, sheet 1 is a master lister with 20 columns of unique information for each user id. The second sheet is a shorter list of User Ids I need information on. The master list has multiple columns like age, sex, etc., but I only need age for a smaller subset of Ids. So is it possible for me to create a new sheet with the matching user ids and a column with the related age?  @Hans Vogelaar 


Let's assume that ID is in column A on both sheets, and age is in column K on the master sheet.

In the first available column on the other sheet (with the shorter list), enter the following formula in row 2:


=VLOOKUP(A2, 'Master Sheet'!$A$2:$K$1000, 11, FALSE)


(K is the 11th column). If you have Microsoft 365 or Office 2021, you can also use


=XLOOKUP(A2, 'Master Sheet'!$A$2:$A$1000,'Master Sheet'!$K$2:$K$1000)


Change Master Sheet to the actual name of your master sheet, and enlarge the ranges if your list has more than 1000 rows (it doesn't matter if it has fewer rows).

Finally, fill or copy down.