Evolution of VLOOKUP in Google Sheets

Most of us start with a simple VLOOKUP but as our needs evolve — the tables grow and our requirements grow with them.

You can build your way up to more complex solutions. Use this walk-thru to reach a formula like this:

=INDEX(
  LET(
    dt, A4:A,
    search_return, {Data!H4:H, Data!E4:E, Data!B4:B, Data!F4:F},
    cols, SEQUENCE(1, COLUMNS(search_return) - 1, 2),
    last, MAX(FILTER(row(dt), dt <> "")),
    look, OFFSET(dt,, , last - row(dt) + 1),
    vl, VLOOKUP(look, search_return, cols, ),
    vl
  )
)

Stage 1: Basic VLOOKUP

The basic formula pattern looks something like:

=VLOOKUP(A4, data, column, FALSE)

1. A4 is what you’re “looking” for, or the value you want to find in the table.
2. data is where you’re looking, whether that’s in the same sheet or another sheet.
3. column is which column number in the table contains the value we want to return.
4. FALSE means we want an exact match.

Stage 2: Reducing FALSE

Sometimes you might want to leave the final FALSE off your VLOOKUP.

By keeping the comma, we can omit the FALSE and have it treated as FALSE.

=VLOOKUP(A4, data, column, )

=VLOOKUP(A4, data, column, FALSE) is the full version.

=VLOOKUP(A4, data, column, ) is the shorted version.

Stage 3: Searching in Any Column

VLOOKUP looks for the value you’re looking for in the first column of your data and then return a value from another column to the right.

=VLOOKUP(A2, B2:C, 2, FALSE)

That’s all fine and dandy but what if you wanted to look something up but you wanted to return a value THAT ISN’T the next column over? Welcome “Array Literals” to the stage!

=VLOOKUP(A2, {C2:C, B2:B}, 2, )

Explanation:

Here, we are looking up A2 within C2:C but returning a value from B2:B.

This setup allows us to return a value from any specified column rather than just the next one.

Drag columns C and B — formula won’t crash.

Don’t worry about the trailing comma at the end… sort of. That trailing comma is just ensuring an exact match in the lookup value as I explained before.

Stage 4: Multiple Column Vlookup

If you ever need to vlookup against multiple columns and return values from one of those columns you can extend the data array and use array formula:

=ARRAYFORMULA(VLOOKUP(A2, {C2:C, B2:B, E2:E}, {2, 3}, ))

Explanation:

The array {C2:C, B2:B, E2:E} selects the 1 column you want to search in and 2 columns you want to return values from.

The list {2, 3} says to return the values from the 2nd and 3rd columns in the array.

I use this formula a ton when using vlookup multiple times in a row or needing to search against multiple columns.

It’s much simpler to read and usually performs better than multiple vlookup for me.

Stage 5: Cross-Sheet Vlookup

Use these inter-sheet lookups:

 =ARRAYFORMULA(VLOOKUP(A2, {Data!C2:C, Data!B2:B, Data!E2:E}, {2, 3}, )) 

Explanation:

This formula looks up A2 in Data!C2:C and returns data from both Data!B2:B and Data!E2:E.

Stage 6: Using INDEX for Array Handling

Shorten again:

=INDEX(VLOOKUP(A2, {Data!C2:C, Data!B2:B, Data!E2:E}, {2, 3}, )) 

INDEX acts just like ARRAYFORMULA, so you get the same result.

Stage 7: Using SEQUENCE for Automation

Here’s how to DYNAMICALLY choose any column(s) out of a list.

Super simple, yet saves me from many a:s:

=INDEX( VLOOKUP( 
   A2, 
  {Data!C2:C, Data!B2:B, Data!E2:E}, 
   SEQUENCE(1,2,2), 
)) 

SEQUENCE generates a list of columns {2, 3}.

SEQUENCE(rows, columns, start, step)

• rows: The number of rows for the sequence to fill.
• columns: The number of columns for the sequence to fill.
• start (optional): The starting value for the sequence. It defaults to 1.
• step (optional): The amount by which each value in the sequence goes up by. Again, this will default to 1.

No more “Hi, remember to drag columns B:Z on ‘other sheet’ again please?

Stage 8: Enhanced Readability with LET

The LET Function is a godsend 👼

When it comes to formulas, the most complicated part of a really long formula is not the operations WITHIN the formula… it’s dealing with ALL.

Introducing: LET:

=INDEX(
  LET(
    dt, A4:A,
    search_return, {Data!H4:H, Data!E4:E, Data!B4:B, Data!F4:F},
    cols, SEQUENCE(1, COLUMNS(search_return) - 1, 2),
    VLOOKUP(A2, search_return, cols, )
  )
)

For those that might not have used/seen LET in Google Sheets yet, here’s a quick explanation:

LET allows you to assign ranges, or (in this case) calculated arrays to NAMES.

So, in this example, I have a number of calculations in the LET part of the function.

They all get a meaningful name: dt, search_return, cols

Then, rather than using the formula itself, I get to use those names in the formula.

The names here are really nice, too because they let me think of a possibly pretty tedious calculation (searching a huge log of decisioning data associated for a few attributes).

This calculation will theoretically as readable for the next data pull I need to do and just as easy to update the search results we’re VLOOKUP-ing against.

Stage 9: Dynamic Range and Performance Optimization

Calculate the last non-empty row in a formula, then use OFFSET to limit your search in VLOOKUP.

Speed up dynamic calculations and drop hundreds of rows of empty cells by filtering out all rows after your latest data point.

=INDEX(
  LET(
    dt, A4:A,
    search_return, {Data!H4:H, Data!E4:E, Data!B4:B, Data!F4:F},
    cols, SEQUENCE(1, COLUMNS(search_return) - 1, 2),
    last, MAX(FILTER(row(dt), dt <> "")),
    look, OFFSET(dt,, , last - row(dt) + 1),
    vl, VLOOKUP(look, search_return, cols, ),
    vl
  )
)

Explanation:

• dt: the data range
• search_return: what columns we are looking at, and what columns we are returning
• cols: columns are indexed automatically
• last: find the last non empty row to limit the OFFSET
• look: limit the OFFSET range with OFFSET
• vl: now execute the VLOOKUP over this dynamically adapted data set
• INDEX: convert result to array formula

The OFFSET makes your calculations 99 times faster in Sheets:

Stage 10. Wrap the formula

Create a user-defined function if you need it but it only takes two parameters:

dt (the data range) and search_return.

From now on you have a super-vlookup at your fingertips:

=SUPER_VLOOKUP(A4:A, {Data!H4:H, Data!E4:E, Data!B4:B, Data!F4:F})

Conclusion

This was VLOOKUP evolution:

1566 =🪨

1988 =VLOOKUP(A1, C2:E3, 3, FALSE)

2010 =ARRAYFORMULA(VLOOKUP(A2, {C2:C, B2:B, E2:E}, {2, 3},))

2025 =SUPER_VLOOKUP(A4:A, {Data!H4:H, Data!E4:E, Data!B4:B})

2050 =I Don’t care, just do what I want

3050 ❤️

👇🏼👇🏼👇🏼

Sample Spreadsheet ● Twitter Community — Sheets™● About the author

PS. Why no XLOOKUP in here? GUESS :)