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.
Video by SparQLearning showing how to use INDEX() - MATCH(), a nice step-by-step!
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.
The library's collections and services are available to all ISU students, faculty, and staff and Parks Library is open to the public.
