Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupfind-replace

Expand VLOOKUP range across multiple cells?

Maybe I went about this the wrong way initially, but this is what I have. I'm storing my students' grades in a Google Sheets document with a "Reports" sheet that pulls info from the other sheets.

Reports!A1 is the student's name. Day 1 attendance is pulled from the Attendance sheet thusly:

=VLOOKUP(A1,Attendance!A1:Q10,2,false)

Unfortunately, as you can see, I only planned for nine students. At the time, I had only ever had 5, so it seemed like a good number. Well, now I have eleven, and I need to expand the A1:Q10 range to at least A1:Q12. But no amount of Googling has given me a method to do this without manually editing 75 formulas. Surely there's some way, even if it involves exporting to some other format and re-importing, right?

Solution
    • press CTRL + H

    • and do:

      0

    and next time use 1 ARRAYFORMULA instead of 75 VLOOKUPs

    =ARRAYFORMULA(IFNA(VLOOKUP(A1:A, Attendance!A:Q, COLUMN(B:Q), 0)))