VLOOKUP with Multiple Criteria in Google Sheets: The Proper Way

VLOOKUP with multiple criteria involves using two or more lookup values, each located in different columns in Google Sheets.

The standard approach is to concatenate the lookup values (search keys). However, this method may not always be suitable, especially when using comparison operators with the criteria.

For instance, in a table containing doctors’ names, specialties, dates, and availability (yes/no), simply combining the doctor’s name and date won’t effectively retrieve the availability of a specific doctor after a particular date. This scenario requires applying comparison operators directly to the criteria.

In this tutorial, you will learn the correct method to implement multiple criteria in the VLOOKUP function within Google Sheets.

Sample Data:

Doctor Name	Specialty	Date	Availability
Dr. Johnson	Neurology	2024-07-01	Yes
Dr. Johnson	Neurology	2024-07-04	No
Dr. Johnson	Neurology	2024-07-05	No
Dr. Lee	Neurology	2024-07-02	Yes
Dr. Lee	Neurology	2024-07-03	Yes
Dr. Lee	Neurology	2024-07-06	Yes
Dr. Lee	Neurology	2024-07-07	Yes

Here’s how you can arrange the sample data in Google Sheets:

Open Google Sheets.
Enter the above data into cell range A1:D8.

When using this data range in formulas, we will use the range A2:D8, excluding the header row.

This setup will allow you to test the VLOOKUP function with multiple criteria effectively.

VLOOKUP with Multiple Criteria: By Combining Search Keys and Ranges

We often combine search keys and corresponding columns for multiple criteria lookup, which remains effective.

For example, in the above table, if we want to find when Dr. Lee is available, the search keys are located in cells F2 and F3, with “Dr. Lee” in F2 and “Yes” in F3.

You can use the following formula:

=ArrayFormula(VLOOKUP(F2&F3, HSTACK(A2:A8&D2:D8, A2:D8), 4, 0))

Note: The output will be a date value. You can format it back to a date format by applying Format > Number > Date.

VLOOKUP with Multiple Criteria: By Combining Search Keys and Ranges

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

Where:

search_key: F2&F3 (the combined search keys)
range: HSTACK(A2:A8&D2:D8, A2:D8) (while the original range is A2:D8, we’ve combined the columns containing search keys as A2:A8&D2:D8 and horizontally stacked them with the original range using the HSTACK function)
index: 4 (to return the availability date, which is the 3rd column in the original range. Due to the added combined column in the front, it’s now the fourth column)
is_sorted: 0 (indicating an unsorted, exact match of the search key)

We used the ARRAYFORMULA function since we combined two ranges. This approach is also useful if you want to retrieve more than one value by specifying multiple indices in the index argument. For example, using {2, 4} instead of 4 to return both the doctor’s name and the date.

Challenges of Using Combined Search Keys in VLOOKUP

To highlight a drawback of this multiple criteria VLOOKUP approach, consider another example:

How would you find the date of the first availability of a doctor in the Neurology department after a specific date? The criteria are:

Doctor: Dr. Lee
Specialty: Neurology
Date: >2024-07-03
Availability: Yes

This approach won’t work in the above method because one of the search keys includes a comparison operator.

VLOOKUP with Multiple Criteria: By Applying Logical Tests

This is the proper way of using VLOOKUP with multiple criteria in Google Sheets.

In our earlier example, we looked up “Dr. Lee” in the first column and “Yes” (availability) in the last column.

You can use the following formula as an alternative:

=ArrayFormula(VLOOKUP(1, HSTACK((A2:A8=F2)*(D2:D8=F3), A2:D8), 4, 0))

Where:

search_key: 1
range: HSTACK((A2:A8=F2)*(D2:D8=F3), A2:D8)
index: 4
is_sorted: 0

To understand this formula, consider the range argument:

(A2:A8=F2)*(D2:D8=F3) – This expression returns 1 where both conditions are true (“Dr. Lee” matches in A2:A8 and “Yes” matches in D2:D8) and 0 otherwise.

We use the search_key as 1 to look up this range and return the value (date) from the fourth column.

In the original range, the date is in the third column. By horizontally stacking the logical test output before the actual range A2:D8 using HSTACK, we shift the original range one column ahead.

This method effectively handles multiple criteria in a VLOOKUP, including scenarios where logical tests with comparison operators are necessary. Let’s see one more example.

Advanced VLOOKUP: Handling Comparison Operators within Search Criteria

In our table, I want to find the available date of Dr. Lee in the Neurology department after July 3rd, 2024.

The criteria are in F2:F5 where:

F2 contains “Dr. Lee” (Doctor’s Name)
F3 contains “Neurology” (Specialty)
F4 contains “2024-07-03” (Date)
F5 contains “Yes” (Availability)

Formula:

=ArrayFormula(VLOOKUP(1, HSTACK((A2:A8=F2)*(B2:B8=F3)*(C2:C8>F4)*(D2:D8=F5), A2:D8), 4, 0))

Where:

search_key: 1
range: HSTACK((A2:A8=F2)*(B2:B8=F3)*(C2:C8>F4)*(D2:D8=F5), A2:D8)
index: 4
is_sorted: 0

I hope this example clarifies how to properly use VLOOKUP with multiple criteria in Google Sheets.

Wrap-Up

VLOOKUP can be adapted for multiple criteria lookups by utilizing workarounds such as adding a column to the original range that accommodates the combined criteria.

In one approach, we combine multiple criteria into a single search key and merge corresponding columns to perform the lookup based on this combined key.

In another method, logical tests are used to return 1 for all matches or 0 when not all criteria match, and then VLOOKUP searches for this result.

The latter method supports the use of comparison operators and is the recommended approach for utilizing VLOOKUP with multiple criteria in Google Sheets.

If the output is a date, the formula will return date values. In such cases, format the result back to the date format.

The formula typically requires ARRAYFORMULA support when combining ranges or applying logical tests across ranges.

Resources

Retrieve Multiple Values Using VLOOKUP in Google Sheets (Multiple Index Numbers)
VLOOKUP – Find a Search Key in Multiple Columns (Matrix) in Google Sheets
VLOOKUP and Offset Multiple Criteria in Google Sheets (Array Formula)

39 COMMENTS

Thanasis May 1, 2022 At 5:42 pm

Hello, thank you very much for this extensive coverage of this topic. It is priceless.

I am trying the example with the multiple criteria I can’t get it to work. I am posting the copy that is not working,

– link removed by admin –

Greetings

- Prashanth May 2, 2022 At 3:29 pm
  
  Hi, Thanasis,
  
  The issue lies in the IFERROR part.
  
  Syntax: IFERROR(value, [value_if_error])
  
  In value_if_error, you have put a space and 0 instead of 0. Just correct that.
  
Andrey January 29, 2022 At 5:21 pm

Hi Prashanth,

Help me. Please give me a hint on how to do it.

I need to calculate column B/F under the condition that the dates must match.

An example highlighted in yellow: B15/F4

— link removed by admin —
(with editing rights)

- Prashanth January 31, 2022 At 11:18 am
  
  Hi, Andrey,
  
  You were looking for the correct function. We can use VLOOKUP as below.
  
  =ArrayFormula(ifna(vlookup(E2:E;A2:B;2;0)/F2:F))
  
  The IFNA() removes #N/A! errors if any.
  
  - Andrey January 31, 2022 At 7:02 pm
    
    Thank you! You helped me a lot!
    
    May I ask this question?
    
    I need to copy a formula to cells A1, C1, E1, etc. The insertion interval is the same.
    
    The range A10-A20 contains values for formulas.
    
    The formula should take the values as below.
    
    A1 – A10, C1 – A11, E1 – A12, etc.
    
    - Prashanth February 1, 2022 At 11:31 am
      
      Hi, Andrey,
      
      It’s not related to the topic. But I will try to answer.
      
      If you want the formula to copy to A1, B1, and C1, this approach may work – Drag a Formula across But Get the Reference Down in Google Sheets.
      
      But you want to copy to every alternative column. So I have a helper row approach.
      
      Empty row # 2 and insert the below array formula in A2.
      
      =ArrayFormula(if(iseven(column(A1:1)),1/0, transpose(sequence(rows(A10:A20)*2)-sort({sequence(rows(A10:A20)*2); sequence(rows(A10:A20)*2)}))))
      
      In A1, insert the following formula, which you can copy to C1, E1, G1, etc.
      
      =iferror(INDIRECT("A"&10+A2))
      
      - Andrey February 3, 2022 At 1:29 pm
        
        Hi Prashanth,
        
        Where do you need to correct the formula so that the alternation is not only through the column, but let’s say the alternation goes at any interval?
        
        For example, start in column A, copy to column H, then to column O.
        
        What elegant formulas you get!
        
        Thank you very much!
        
        
        Prashanth February 5, 2022 At 10:40 am
        
        Hi, Andrey,
        
        My earlier formula is not flexible enough to address it.
        
        Here is the flexible one. Here I take the liberty to use one more helper cell to control every N, i.e., cell A3.
        
        In cell A3, enter 7 or the number that controls every alternate column.
        
        Replace my earlier formula in cell A2 with the below one.
        
        =ArrayFormula(if( countifs(transpose(flatten(substitute(sequence(rows(A10:A20),1,0), "",SEQUENCE(1,A3)))),transpose(flatten(substitute(sequence(rows(A10:A20),1,0), "",SEQUENCE(1,A3)))),column(indirect("A2:"&address(2,rows(A10:A20)*A3,4))), "<="&column(indirect("A2:"&address(2,rows(A10:A20)*A3,4))))=1, transpose(flatten(substitute(sequence(rows(A10:A20),1,0), "",SEQUENCE(1,A3)))),1/0))
        
        A tutorial that contains the formula explanation is in the pipeline.
        
bahar January 27, 2021 At 1:07 pm

Hi Prashanth 😊,

how to sum A:A if multiple same models in my sample sheet? the formula is in cell G2.

— URL of the sheet removed by the admin —

- Prashanth January 27, 2021 At 4:52 pm
  
  Hi, Bahar,
  
  Vlookup is not a suitable formula for you.
  
  Your table is like this (range A1:C).
  Model | Date | Price
  AB5479 | 1 Jan 2021 | 100.00
  CD7150 | 1 Jan 2021 | 180.00
  AB5479 | 1 Jan 2021 | 165.00
  
  Criteria (E2:F2):
  AB5479 | 1 Jan 2021
  
  Use the below formula in cell G2.
  
  =sum(filter(C:C,A:A=E2,B:B=F2))
  
  If you enter more criteria below E2:F2, then you should drag the above formula down.
  
  But you can replace the above formula with the below Sumif array formula in cell G2 which will automatically expand down.
  
  =ArrayFormula(if(len(E2:E),(sumif(A:A&B:B,E2:E&F2:F,C:C)),))
  
  Both the formulas entered in your sheet.
  
Manisahhh December 30, 2019 At 1:21 pm

Hi Prashanth 🙂

Based on your examples, is it possible to use Vlookup with single criteria (“Safety Helmet”) & return all of the matching details?

For example, return all Safety Helmet details in each row. (in form of a table)

ITEM | AREA | AMOUNT
Safety Helmet | North | 70.00
Safety Helmet | West | 80.00
Safety Helmet | East | 75.00

Thank you !!

- Prashanth December 30, 2019 At 5:02 pm
  
  Nope! For that use Filter or Query.
  
  Assume the dataset is in the range A1:C.
  
  Filter:
  
  =filter(A1:C,A1:A="Safety Helmet")
  
  Query:
  
  =query(A1:C,"Select * where A='Safety Helmet'")
  
  See my function guide to learn these two and other popular/useful Google Sheets functions.
  
  - Manisahhh December 31, 2019 At 6:54 am
    
    Thank you so much !! It works 🙂
    
Irina April 13, 2019 At 7:37 pm

Hi, thanks for this. I presume this would not work with 2 drop downs? I’m trying to apply the same method but getting an error.

- Prashanth April 14, 2019 At 9:20 am
  
  Hi, Irina,
  
  Can you please replicate the error on a demo sheet and share it with me here?
  
Brosserg January 11, 2019 At 12:40 am

OK so I used my USA locale Sheet with your formula then when I changed it’s locale to France it auto adjusted my formula to this : =ArrayFormula(IFERROR(VLOOKUP(A2:A&B2:B; {E1:E&F1:F\ G1:G}; 2; 0 ); 0))

So it changed “;” to a “\” and I never would have guessed that.

- Prashanth January 11, 2019 At 8:55 am
  
  Hi, Brosserg,
  
  I guess the formula is working now.
  
  See this supporting tutorial.
  
  How to Change a Non-Regional Google Sheets Formula.
  
  Best,
  
Kay November 15, 2018 At 11:34 pm

Hi,

I need something like this but combined with an importrange function.

Basically, I have one list with extensive data (tour data of artists) and I need to return just some of the sales-figures into another sheet.

So, in File Marketing it should pull the value of File Sales, column I IF in File Sales the artist name is the same as in Column A in File Marketing. So far, that works with Vlookup & importrange.

BUT – there is a second criterion that needs to match in both sheets in order to pull the correct value.

So only if in File Marketing the column A (Artist name) AND column B (date) are the same as in the respective artist & date columns in sheet Sales, I want the value of column I in sales pulled into sheet marketing.

So far, this is the formula that works:

=vlookup(A2:A, Importrange("URL","'Presales '18'!D2:I"),6,0)

with your tutorial, I tried to modify it into

=ARRAYFORMUL(iferror(vlookup(A2:A&B2:B,{Importrange("URL","'Presales '18'!D2:d&G2:g,h2:I")},2,0)))

but nothing shows up – but also no error or parse warning.

I guess the area and column index is wrong, but I don’t know how?

Thanks a lot, Kay

- Prashanth November 16, 2018 At 9:30 am
  
  Hi, Kay,
  
  Let me cross check your question and I will possibly come back with a tutorial that addresses it by today itself. I will post the link here then.
  
  Thanks.
  
- Prashanth November 16, 2018 At 3:14 pm
  
  Hi, Kay,
  
  As promised, here is the tutorial.
  
  How to Vlookup Importrange in Google Sheets [Formula Examples]
  
  You can use either Query or Vlookup with Importrange to get your expecting result.
  
Samah Sohrab November 3, 2018 At 12:16 am

Hi,

Is there a way to not require creating a new combined column, “Additional lookup Column”?

Thanks

- Prashanth November 3, 2018 At 6:15 am
  
  Hi,
  
  It’s already included in this tutorial. Seems you have missed that.
  
Corissa August 14, 2018 At 8:26 am

Hi –

This information was very helpful for a project I’m working on!

I seem to be having a similar issue as others who have commented. I tried to work through the formula differently based on your responses to them, but I’m still getting “0” as the result & the index was being recognized as part of the range.

Note: Link removed by the admin.

=ArrayFormula(iferror(vlookup(A2&B2, {A6:A11&B6:B11, C6:C11},, 2, 0 ), 0))

I feel like I’m missing something really basic/obvious, but I can’t seem to figure it out. Your help is most appreciated!

- Prashanth August 14, 2018 At 11:55 am
  
  Hi, Corissa,
  The formula in your sheet is;
  =ArrayFormula(iferror(vlookup(A2&B2, {A6:A11&B6:B11, C6:C11},, 2, 0 ), 0))
  It should be like this.
  =ArrayFormula(iferror(vlookup(A2&B2, {A6:A11&B6:B11, C6:C11}, 2, 0 ), 0))
  An extra comma in your formula makes that issue.
  Hope this solves the issue.
  Thanks
  
SWWeatherGuy July 25, 2018 At 11:43 pm

I’m trying to use this formula but not getting the desired result, assuming I’m not misunderstanding something. My formula is written:

=ArrayFormula(IfError(VLookup($B$1 & C2, {$B$11:$B & C$11:C, $A$11:$A}, 1, 0), 0))

B1= An entered value of 1-31 days of the month [current value 24]

C2= A Max result from a list of temperature values for the B1 day of the month =Max(Filter(C$11:C,$B$11:$B =$B$1)) [current value 84.3]

A11: A= A list of four-digit year values ranging from 1995 to 2017

Instead of the corresponding year value from A11: A, the result I’m getting is the “search key” [B1&C2] of 2484.3

What am I not understanding?

- Prashanth July 26, 2018 At 8:07 am
  
  Hi,
  
  The column Index should be 2.
  
  Try this.
  
  =ArrayFormula(IfError(VLookup($B$1 & C2, {$B$11:$B & $C$11:C, $A$11:$A}, 2, 0), 0))
  
  a
  
  - SWWeatherGuy July 26, 2018 At 9:25 pm
    
    That indeed worked, thank you! Can you explain why the column index is 2 in this example?
    
Lubos June 27, 2018 At 9:20 pm

Hi Prashanth,

thanks a lot for the tutorial, but I have two questions:
1, in ARRAY function =ArrayFormula({A5:A&B5:B, C5:G}) to what serves the second argument “C5:G”?

2, could you help me to check my equation here:

https://docs.google.com/spreadsheets/d/1K4H6TlTZNJje8Atdf1AgH42UXQ-aAE2q4qy_kqPCAVI/edit?usp=sharing

Basically, the cell I25 should produce value from column X20 if and only if both D25=O20 and K25=T20

Your simple method 1, would work but requires one more redundant column and 2, I cant make work…

Any help much appreciated

- Prashanth June 28, 2018 At 9:25 am
  
  Hi, Lubos,
  
  I’ve checked your sheet. Here is your formula that I’ve found there.
  
  =ARRAYFORMULA((VLOOKUP(D25&K25,{O20:O23&X20:X23},9)))
  
  And see my working Vlookup formula with multiple criteria.
  
  =ARRAYFORMULA((VLOOKUP(D25&K25,{O20:O23&T20:T23,X20:X23},2)))
  
  The logic is simple.
  
  This combines the search keys
  
  D25&K25
  
  So you should combine the corresponding two columns into one.
  
  O20:O23&T20:T23
  
  Now you have only one column. You want the value to return from another column, i.e. from X20: X23.
  
  The below formula makes a two column array and you want the formula to return the value from the column 2 in this array.
  
  {O20:O23&T20:T23,X20:X23}
  
  So I’ve put the index number 2 in Vlookup.
  
  - Lubos June 28, 2018 At 5:09 pm
    
    Hi Prashanth,
    
    thanks a lot, now I understand:-) I got the idea, but in that particular formula you provided the outcome is incorrect. So I25 returns value “6.625”but it should return value of combined “RS3617xs”&”SYNO_A_20180028” which is X20 “213.6”. Actually is seem that value of cell D25 doesnt affect what I25 returns (if you leave it blank, the value will be still “6.625”, but that thats not correct for vlookup)
    
    Sorry I accidentally posted the same question twice. You can delete the other post.
    
    Thanks a lot!!!
    
    - Prashanth June 29, 2018 At 9:16 am
      
      I am sorry about the typo.
      
      I missed the sort order to put at the end. Here is the corrected one.
      
      =ARRAYFORMULA((VLOOKUP(D25&K25,{O20:O23&T20:T23,X20:X23},2,0)))
      
Tamer June 22, 2018 At 7:19 pm

Thanks.
How do I set the range as a named range to use it in VLookup
I tried setting this =ArrayFormula(iferror(vlookup(D12&C12,{G2:G&H2:H,H2:H},2,False)))
I need to set {G2:G&H2:H,H2:H} as a named range so it does not change when I copy the formula between cells but I am unable to do it.

- Prashanth June 23, 2018 At 3:16 pm
  
  Hi, Tamer,
  
  You can not use named ranges here as the formula combines different colums.
  
John Smith April 27, 2018 At 12:42 pm

Clever method! Thanks.

Kris March 24, 2018 At 2:43 am

Thank you!! Awesome!

Konrad March 6, 2018 At 5:25 pm

=ARRAYFORMULA(iferror(VLOOKUP(M17 & N17; {price!A2:A & price!B2:B ; price!C2:C}; 3; 0);0))

Something like that I tried to set also without additional column. But that didn’t worked. It always gave me the second zero (0) which is in iferror function. And also when I parse through VLOOKUP function after going out from { } that number 3 was still interpreted as `range` instead of `index`

- Prashanth March 6, 2018 At 7:03 pm
  
  Hi Konrad,
  
  I tried your formula on my sheet and it works after little modification.
  
  =ARRAYFORMULA(iferror(VLOOKUP(M17 & N17, {price!A2:A & price!B2:B , price!C2:C}, 2, FALSE),0))
  
  The Column Index is 2 not 3. Also based on your locale settings, please change the comma in my formula to semicolon or vice versa.
  
  Please try this and let me know.
  
  Thanks.
  
  - Hugo May 15, 2018 At 6:57 pm
    
    Hi Konrad,
    
    I’ve got the same problem and I can’t find the solution… It always answer 0 even when I change the index number
    
    =ARRAYFORMULA(iferror(VLOOKUP(pich!C2&pich!C3&pich!C4;{arbo!A2:A & arbo!C2:C & arbo!F2:F;arbo!G2:G};6;false); 0))
    
    - Prashanth May 15, 2018 At 7:48 pm
      
      Hi Hugo,
      
      You have only 4 columns (A, C, F, G) and you can’t use index number 6 in Vlookup.
      
      Thanks.