Google Sheets: VLOOKUP with key in any column (not necessarily the first column!)

Ah, vlookup… So handy. Let’s you get a value from one column by searching for a specific “key” in another column. But you know there’s one thing about vlookup that has always annoyed the hell out of me. (It’s probably annoyed you, too.)

The “key” has to be the first column in your array. This is a pain in the ass because… Well, let’s illustrate with an example.

Suppose you’ve got two sets of data: a “client_account_managers” table, and a “client_performance” table.

client_account_managers

	A	B
1	Account Manager	Client
2	Alice	Casey’s General Store
3	Bob	Hy-Vee
4	Carol	Des Moines Music Coalition

client_performance

	A	B	C
1	Client	Account Manager	Performance
2	Des Moines Music Coalition		10
3	Hy-Vee		20
4	Casey’s General Store		30

In our “client_performance” sheet, we don’t know who each client’s account manager is. So, we want to look it up from the “client_account_managers” sheet. And, of course, we’d like to use just one formula (an array formula in cell B2) that will look up each client’s account manager an populate the right values into each row in column B.

So, we start writing our vlookup array formula…

=ARRAYFORMULA(VLOOKUP(

We know the client name. That’s what we want to use as our search key.

=ARRAYFORMULA(VLOOKUP(A2:A,

We know that we’re going to lookup the account managers from the “client_account_managers” table and that the two columns we need are columns A and B.

=ARRAYFORMULA(VLOOKUP(A2:A,client_account_managers!A:B,

And here’s where we run into trouble. In our client_account_managers sheet, our search key appears to the right of the column we want to get. And VLOOKUP requires that the search key be the first column in the lookup range. In other words, VLOOKUP can’t lookup the account manager from the “client_account_managers” sheet because “client” (the thing we know) appears after the “account manager” column.

This has frustrated me for years. Like, seriously, vlookup? Why can’t I just tell you which column contains the key? (sigh)

Fortunately, there’s a way around this limitation.

What we need to do is, just for our VLOOKUP, we need to switch up the order of the columns. We want our VLOOKUP to “see” the “client” column as the first column in our range and “account manager” as the second column.

But how the heck are we gonna do that?

Why, by creating an array, of course!

(When this first occurred to me, I literally did a face palm. And then gave myself thirty lashes for not bothering to work this out years ago.)

So, here it is.

=ARRAYFORMULA(VLOOKUP(A2:A,{client_account_managers!B:B,client_account_managers!A:A},2,FALSE))

See what we did there? Our array (with the curly brackets) sets column B as the first column, and column A as the second. So, the VLOOKUP sees this instead:

	A	B
1	Client	Account Manager
2	Casey’s General Store	Alice
3	Hy-Vee	Bob
4	Des Moines Music Coalition	Carol

Now the search key is the first column (as far as the VLOOKUP is concerned), and we’re able to lookup each client’s account manager. Hooray!

	A	B	C
1	Client	Account Manager	Performance
2	Des Moines Music Coalition	Carol	10
3	Hy-Vee	Bob	20
4	Casey’s General Store	Alice	30

See it in action here!

Side note:

What about INDEX / MATCH? Couldn’t you use MATCH to find the row number, and then use that in the INDEX function, returning the first column in the array?

Yes… but no.

INDEX + MATCH will work… but not in an array formula. Whomp whomp. And I know I don’t have to tell you… when working with multiple rows of structured data, you definitely want to avoid the old “copy the same formula a thousand times down the same column” tactic and instead use the beautiful, elegant, efficient, and significantly less error-prone array formula (or one of its variants such as FILTER).

Subscribe to Learn More Google Sheets Secrets