Description of how I used INDEX & MATCH in Excel to look up data from the org table for another table.

excel-index-match-lookup.md

Notes on Using Excel's MATCH and INDEX Functions

To help me get through this faster in future.

Data:

Sheet Authors has various data about a person, including their Org code in Column C.

Sheet Orgs has Column A with the Org code (matching Column C's codes) and Column B with the Title of the Organization.

Problem

I want to add a column, Organizations, in Authors to use the value of Column C to get the title of the organization out of Column B from Orgs. I also don't want an ugly #N/A for authors that don't have an org code or for situations there there's nothing in the other table.

Solution

=IFERROR(INDEX(Orgs!$B$2:$B$206,MATCH(C2,Orgs!$A$2:$A$206,0)),"")

MATCH - First argument is the column I'm matching from. Second argument is the values I'm matching against. Third argument is a much-needed 0 to force exact matches only. We don't want a match if there isn't one. This is used to get the ROW information we're looking at in...

INDEX - First argument is the data I'm pulling, the title. Second argument is the row, which we derive from MATCH. We're only looking at one column, so we don't need a third argument.

IFERROR - Just cleans it up and solves my issue by saying that if there's an error that'd return #N/A, we just return "" instead.