Excel Matching

Copper Contributor

I have a list of Museum Numbers and Catalogue Numbers.  (1000s)


On a separate excel I have a shorter list of museum numbers. Is there a quick way for me to automatically match the shorter list with the catalogue numbers without looking up each museum number on the 1st excel and copying the catalogue number across. ?



2 Replies


You can use Excel's VLOOKUP function to quickly match the museum numbers from your shorter list with the corresponding catalogue numbers from your longer list.


Here's how you can do it:

Assuming your shorter list of museum numbers is in column A of Sheet1 and your longer list (containing both museum numbers and catalogue numbers) is in columns A and B of Sheet2, you can follow these steps:

 1. In the first cell of column B in Sheet1 (next to the first museum number), enter the following formula: =VLOOKUP(A1, Sheet2!$A$1:$B$1000, 2, FALSE)

This formula searches for the museum number in cell A1 of Sheet1 within the range A1:B1000 in Sheet2. If a match is found, it returns the corresponding value from the second column of the range (i.e., the catalogue number).

 2. Press Enter to apply the formula. This will display the corresponding catalogue number next to the museum number in the first row.

 3. Drag the fill handle (the small square at the bottom-right corner of the cell) down to fill the formula for the rest of the museum numbers in your list.


This will automatically match each museum number in your shorter list with its corresponding catalogue number from the longer list without manually looking up each one.


Just make sure to adjust the range Sheet2!$A$1:$B$1000 to encompass all the data in your longer list. If your longer list contains more than 1000 rows, you can adjust the range accordingly. The text, steps and function were created with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.


If both workbooks are open it is possible to use XLOOKUP between master and slave workbooks.


(even if the file names are mixed up - sorry)