Skip to Main Content

Microsoft Tips and Codes

Codes, tips and tricks found over years of experience using Office.

LOOKUP() - the trials and triumphs

Red DiamondRed Diamond

LOOKUP() is a good tool for finding specific text from one spreadsheet to another PROVIDED the second spreadsheet is a subset of the first.

For example, let's say we have a spreadsheet of 3000 rows of information. We filter this list to distribute it to a group for comment, but forgot to add one column after spending a lot of time preparing the second spreadsheet. Using LOOKUP() can populate that column from the first one if a unique ID has been established. In the following example, the CALLNO was mistakenly left off the copied table:

BIB COLLECTION CALLNO TITLE
2225 PRGEN   Who was who among North American authors, 1921-1939.
2572 PGEN   Contemporary Novelists, 2nd ed
23592 PGEN   Art education: a guide to information sources
31249 PGEN   Encyclopedia of information systems and services
32608 PGEN   Children's authors and illustrators

I can use LOOKUP() to find the call number for each of these with ease using the unique BIB in the first column. NOTE THE DOWNSIDE: If there are any repeat numbers in the first column, LOOKUP() will repeat the first record it finds with the information. AND, you must have the ID number arranged in descending order for both lists for it to be halfway successful.

A lot of limitations! So let's look at a better solution.

INDEX() - MATCH()

Video by SparQLearning showing how to use INDEX() - MATCH(), a nice step-by-step!

Another more advanced INDEX() - MATCH()

A bit more complex version by ExcellsFun! The beauty of this is that your list does not have to be sorted. The concatenation of two columns for the match lookup makes this possible.

Subject Guide

Profile Photo
Megan O'Donnell
she / her
Contact:
150 Parks Library
515-294-1670