How to INDEX multiple columns

I needed to check multiple columns in different spreadsheets and output the matching result. I wasn’t sure that VLOOKUP would work for this case and INDEX-MATCH is supposed to be better anyway, so I used the following code. It uses IFERROR to nest MATCH functions:

=INDEX('SpreadsheetA'!A:A, IFERROR(MATCH(A59,'SpreadsheetA'!P:P,0),IFERROR(MATCH(A59,'SpreadsheetA'!Q:Q,0),(IFERROR(MATCH(A59,'SpreadsheetB'!K:K,0), MATCH(A59,'SpreadsheetB'!J:J,0))))))

The above checks for matches in colums A, Q, and P in SpreadsheetA, and columns K and J in SpreadsheetB. This example does not include error checking, as each of the entries in field A59 are already UNIQUE. Don’t ask me how you’d do that — I don’t know :-)

Tagged , , , , ,

About Jenny Smith

I'm Jenny Smith. I keep up with three cars, two kids, one husband, and I live on 300+ acres of rolling farmland in Northern Virginia where we look after an elderly relative. I like tomatoes, all things Star Trek, watercolor, and reading. I spend most days in the garden fighting deer and groundhogs as I pursue a graduate degree. I'm trying to be like Jesus -- emphasis on the trying.
View all posts by Jenny Smith →

Leave a Reply

Your email address will not be published. Required fields are marked *