blue wordpress logo How to INDEX multiple columns

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 :-)

Posted by Jenny Smith

I'm Jenny Smith. I blog about life on the 300+ acres of rolling farmland in Northern Virginia where I live. I like tomatoes, all things Star Trek, watercolor, and reading. I spend most days in the garden fighting deer and groundhogs while trying to find my life's meaning. I'm trying to be like Jesus -- emphasis on the trying.