# 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)`

`A4`

is what you’re “looking” for, or the value you want to find in the table.`data`

is where you’re looking, whether that’s in the same sheet or another sheet.`column`

is which column number in the table contains the value we want to return.`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` `

and returns data from both **Data!**C2:C

and **Data!**B2:B

.**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}, )) `

acts just like **INDEX**`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),

))

generates a list of columns **SEQUENCE**`{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 :)