How to Vlookup adjacent cells in Google Sheets? Before answering this you must know what I meant by the term adjacent cells.

If you consider the search_key, the adjacent cell can be the cell immediately right to the search_key cell in the range.

But when you consider the output of a Vlookup, the adjacent cell can be the cell up, cell down and also a cell right to the Vlookup result cell.

I’ll address (elaborate on) both these scenarios. For the first problem, we can use Vlookup itself. But for the second problem (to some extent for the first problem too), Index-Match is the better solution.

Cheque Received from Customers – Sample Data

(Data Range A1:E5)

Let’s understand the data first.

Assume I’ve received a few payments from some of my customers and have the details properly recorded in a Google Sheets file.

You May Like: Allocate Payment Receipts Against Invoices in Google Sheets.

Let’s see how to use a customer name to find the cheque number right to the customer name, once found, then find the cheque received prior or later to the found cheque.

Vlookup Adjacent Cells in Google Sheets

How to find the cheque number received from the customer “C”?

Find the Value Next (Right) to the Search_Key Using Vlookup

Syntax:
VLOOKUP(search_key, range, index, [is_sorted])

In Vlookup, the customer “C” will be the search_key.

Vlookup can search this key and return any value from the immediate cell to its right or you can say adjacent cell.

Note: For getting value from an adjacent cell to the left of a Vlookup search_key, use reverse Vlookup. For example, the search_key is cheque # 271058 and what you want to find is the customer name (adjacent cell, left of search_key).

Here is the Vlookup adjacent cell formula that works in Google Sheets.

=vlookup("C",A2:E,2,0)

You can use a cell reference (G1) as the search_key as below.

=vlookup(G1,A2:E,2,0)

Find the Value Next (Right) to the Search_Key Using Index-Match

As you can see in the just above screenshot, the search_key is in cell G1. Using the Match function in Google Sheets, we can match the search_key “C” in column A.

=match(G1,A2:A,0)

This would return 3 as the customer name “C” is available in the third row in the range A2:A. With Index, we can offset 3 rows in column B.

=index(B2:B,match(G1,A2:A,0))

That means we can use an Index-Match combination as above as an alternative to Vlookup adjacent cell formula in Google Sheets.

Index-Match to Return Value from Up and Down to the Vlookup Output Cell

It’s easy to remember a customer name than a cheque number issued by that customer, right?

As an example, assume, I know that I have received one cheque from the customer “C”.

What I am not remembering is the cheque number received from customer “C” as well as from whom I have received cheques immediately before and after.

Using Vlookup and Index-Match we can find the cheque number received from customer “C” which I have already explained with formulas above. The rest of the problem we can solve with Index-Match.

Understand the above problem better with this screen capture.

We have already the Vlookup and Index and Match combination formula to return the adjoining cell to the right side of the search_key.

That formulas return the cheque number 271058. Let’s see how to get the adjoining cell values up and down to this cheque number (Vlookup or Index-Match result)

Get the Value from One Cell Up to the Vlookup Result Cell

The key in the Index-match combination is the row offset using the Match function.

In the earlier example, the Match formula returned number 3 because the customer “C” is available in the third row of the range.

Deduct 1 from it (Match formula) to get one cell up to the Vlookup result value.

=index(B2:B,match(G1,A2:A,0)-1)

This Index and Match formula will return the cheque number 271057 as the formula offsets 2 rows (3-1).

Get the Value from One Cell Down to the Vlookup Result Cell

To get the value from once cell down to the Vlookup result cell, use this formula.

=index(B2:B,match(G1,A2:A,0)+1)

Result: 912776

Conclusion

Are you a Vlookup enthusiast and don’t want to use Index-Match? Then read on.

Without using Index and Match we can solve the above (last two) problems!

How?

let’s once again go through the Vlookup formula that we have used to find the adjoining cell right to the search key.

=vlookup(G1,A2:E,2,0)

Modify this formula as below,

=cell("address",vlookup(G1,A2:E,2,0))
Result: $B$4

Now we must separate $B$ and the number 4 from the above result. The following two formulas do that.

=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")
Result: $B$
=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[\d]")
Result: 4

To get a value from once cell up to the Vlookup output value, deduct 1 from the second REGEXEXTRACT and then combine both formulas using &.

=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")&regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[\d]")-1
Result: $B$3

Needless to say plus 1 to get the value from one cell down to the Vlookup result cell.

=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")&regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[\d]")+1
Result: $B$5

Indirect either of the formulas and see what it returns.

Example:

=indirect(regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")&regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[\d]")+1)
Result: 912776

That’s all about how to Vlookup adjacent cells in Google Sheets.