We can Vlookup and offset multiple criteria using an array formula in Google Sheets. This post has one example of such use that you can adapt to some of your data.

The two main reasons that make Google Sheets my choice of spreadsheet app compared to its rival are as follows.

1. The first one is the flexibility in using array formulas (the formula in this post is one such example).
2. The second one is its faster online performance. Even if Google Sheets is a cloud-based application, it’s faster than its rival’s online version.

Our topic, Vlookup and offset multiple criteria, shows the capability of array formula use in Google Sheets.

As usual, let’s start with sample data. I’m furnishing it below in tabular form for you to copy to your sheet for testing.

Name the tab “Item” and copy-paste the above table in the range A1:D10 in that tab.

Here all the P.O. Dates are in one column. What I am going to do is, in another tab, search all the P.O. Nos. and Items and return the Qty in separate date columns.

If you are looking for a solution to match and offset multiple criteria in Google Sheets, this may work in that case too.

But do note that since we are using Vlookup, there will be issues if there are duplicates in the “Item” tab. The formula would match only the first row in the duplicates row.

In the above case, all the values are unique if you consider row-wise, not values in individual cell wise.

Before going to the formula and explanations, see the result that it returns. The formula result is in the tab named “Offset Match”.

As a side note, the first tab named “Item” contains our source data.

Array Formula to Vlookup and Offset Multiple Criteria

If you first get a glimpse of the formula, you might think that it’s very complicated. That’s why I haven’t shown you the same on the image above.

So let’s start step by step to write the array formula to Vlookup and offset multiple criteria in Google Sheets.

There are three steps involved.

1. Multiple criteria Vlookup to match multiple criteria and return quantities.
2. Split the multiple criteria Vlookup result columns using Index (you will get more idea later).
3. Distribute/Offset the Vlookup result into corresponding date columns. For this, I will use a trick that involves IF and Sequence.

Here are the steps to Match/Vlookup and offset multiple criteria in Google Sheets.

Note:- You can see a few formulas under the 3 different steps below. Just enter it in your sheet to understand what it returns and then delete it. We will combine them later.

Vlookup Matching Two Columns in the Source Table

You can test the below formula in the tab named “Offset Match”.

step_1_formula

=ArrayFormula(
     vlookup(
        A3:A11&B3:B11,
        {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},
        {2,3},
        0
     )
)

Vlookup Syntax (so that you can read the formula clearly): VLOOKUP(search_key, range, index, [is_sorted])

The arguments in the Vlookup are as follows.

search_key: A3:A11&B3:B11
range: {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}
index: {2,3}
is_sorted: 0

If you can’t understand it, please read my related guide.

Related: How to Use Vlookup to Return An Array Result in Google Sheets.

Now, in order to Vlookup and offset multiple criteria in Google Sheets, we must split the above result. We can use the Index function for that.

Similar: Select Only the Required Column from an Array Result in Google Sheets.

Split the Vlookup Result columns

On the above image, you can see the two columns returned by the Vlookup.

The Vlookup multi-column array formula is in cell C3 and the results are in the column range C3:C11 and D3:D11.

Note:- The values in C3:C11 are the date values. If you format it using the menu Format > Number > Date, it will retain the date format. But it’s not necessary.

In this second step to Vlookup and offset multiple criteria, I am using Index as below to split the columns.

Generic Formula 1 (to return the first column from the Vlookup result):

=index(step_1_formula,0,1)

We can remove the ArrayFormula from the step_1_formula when using the Index function. So the formula would be;

step_2_formula_1

=index(
     vlookup(
        A3:A11&B3:B11,
        {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},
        {2,3},
        0
     ),
     0,1
)

Generic Formula 2 (to return the second column from the Vlookup result):

=index(step_1_formula,0,2)

The only change that compared to the just above formula is the last number 2 (second column). In the just above formula, it is 1 (first column).

step_2_formula_2

=index(
     vlookup(
        A3:A11&B3:B11,
        {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},
        {2,3},
        0
     ),
     0,2
)

No need to use this formula, earlier formulas, or coming formulas in any cell right now. Just test them and delete them as we will later use them in the IF function.

Now here is step 3 that is the key and a tricky formula in our array formula to Vlookup and offset multiple criteria in Google Sheets.

The Role of IF and Sequence to Vlookup and Offset Multiple Criteria in Google Sheets

First, see the result area in our tab named “Offset Match”.

The formula result area is highlighted in light yellow color, and the range is C3:AG11. There are nine rows and thirty-one columns in the highlighted area.

In C2:AG2 you can see the numbers from 1 to 30. It’s the dates from 01/11/2020 to 30/11/2020 (both in dd/mm/yyyy format).

I have formatted the dates in ‘dd’ format from the Format > Number > More format > Custom number format.

If you want, you can keep the dates instead of dates in ‘dd’ format. I have formatted it to shorten the column width.

Here is the role of the Sequence function in Vlookup and Offset Multiple Criteria in Google Sheets.

Role of Sequence

In cell C3, enter this formula (please refer to the image below).

step_3_formula

=ArrayFormula(sequence(9,31)^0+$C$2:$AF$2-1)

Result:

It copies the dates from the header row C2:AG2 to all the cells in the range (result range). If you want, I’ll explain this tiny formula in a later post.

Role of IF

Here is the final step.

We should combine all the formulas that I have given under different steps above as per the generic formula below.

=ArrayFormula(
     if(
        step_3_formula=step_2_formula_1,
        step_2_formula_2,
     )
)

As per the above generic formula, here is our final formula to Vlookup and offset multiple criteria in Google Sheets.

final_formula

=ArrayFormula(IFNA(if(sequence(9,31)^0+$C$2:$AF$2-1=index(vlookup(A3:A11&B3:B11,{Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},{2,3},0),0,1),index(vlookup(A3:A11&B3:B11,{Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},{2,3},0),0,2),)))

Just included IFNA to avoid N/A errors in the result.

Vlookup and Offset Multiple Criteria Formula in a Larger Dataset (Additional Tips)

What are the changes required to expand the formula to cover more rows? More columns aren’t required as the formula already covers 31 columns, i.e., one whole month.

I will explain how to use the formula to cover up to 100 rows. More rows may impact your Sheet’s performance or may not work because of the limitation of the ampersand sign in the formula.

Here are the changes:

• Change sequence(9,31) to sequence(100,31)
• Replace 11 in all the range references A3:A11 and B3:B11 in the formula to A3:A100 and B3:B100, respectively.
• You can see row number 10 in the ranges Item!A2:A10, Item!B2:B10, Item!C2:C10, and Item!D2:D10 in the formula. Change all that 10 to 100. I mean, change them to Item!A2:A100, Item!B2:B100, Item!C2:C100, and Item!D2:D100.

That’s all about Vlookup and offset multiple criteria in Google Sheets.

Sample_Sheet_181120