VLOOKUP on Two or More Criteria Columns


If you have ever tried to use a VLOOKUP function with two or more criteria columns, you’ve quickly discovered that it just wasn’t built for that purpose. Fortunately, there is another function that may work as an alternative to VLOOKUP depending on what you want to return.

Multi-Column Lookup Objective

First, let’s confirm our objective by looking at a sample workbook. We have exported some information from our accounting system, and it is basically summarizes the transaction totals for the month by class and by account. A sample of the export is shown below:


From this exported data, we would like to retrieve selected amounts based on the class and account columns. We want to retrieve the amounts and place them into our little report, pictured below:


If you are familiar with the VLOOKUP function, it feels natural to try to build the report with this function because, after all, this is a lookup task. And, lookup tasks are best solved with traditional lookup functions…right? Well, it depends. It depends on what you are trying to retrieve.

Conditional Summing for Lookups

If you are trying to retrieve a numeric value, such as an amount, then a traditional lookup function may not be your best bet. Here’s why. Beginning with Excel 2007, Microsoft included the conditional summing function SUMIFS. This multiple condition summing function is designed to add up a column of numbers, and only include rows that meet one or more conditions. Are the dots starting to connect yet?

If we apply this idea to our task at hand, we would quickly realize that we could use this conditional summing function to retrieve our report values.

The first argument of the SUMIFS function is the sum range, that is, the column of numbers to add. In our case, the column that has the value we wish to return. The remaining arguments come in pairs: the criteria range and the criteria value.

It is helpful to think about the function in these terms: add up this column (argument 1), only include those rows where this column (argument 2) is equal to this value (argument 3), and where this column (argument 4) is equal to this value (argument 5), and where…and so on, up to 127 pairs.

Thus, to populate our report, we’ll retrieve the amount values from the export, and match the class and account columns, as shown below.


If there happen to be multiple rows with the same class and accounts, then the SUMIFS function would return the sum of all matching items.

As you can see, if the value you are trying to return is a number, then the SUMIFS function makes it simple to perform multi-column lookups. But, what if the value you are trying to return is not a number? Well, then you’ll need to use a traditional lookup function as discussed below.

Using VLOOKUP with SUMIFS Method

One method is to use VLOOKUP and SUMIFS in a single formula. Essentially, you use SUMIFS as the first argument of VLOOKUP. This method is explored fully in this Excel University post:



If you are trying to return a text string rather than a number, or are using a version of Excel that doesn’t have SUMIFS, then you are probably stuck with using a traditional lookup function such as VLOOKUP along with the CONCATENATE function to generate a single unique lookup column. This approach is fairly well documented, but the basic idea goes like this: create a single lookup column first, and then use VLOOKUP.

Our example will be an employee list, as illustrated below:


We need to retrieve the state from the employee list for our little report shown below:


Since the value we are trying to return, the state, is a text string and not a number, we are precluded from using the SUMIFS function. Thus, we’ll need to go old-school with VLOOKUP and CONCATENATE.

We start by building a helper column that basically creates the combined lookup values. This can easily be accomplished with the CONCATENATE function or the concatenation operator (&). This new lookup column is illustrated in column B below:


Now we have a single lookup column that can be used with a traditional lookup function such as VLOOKUP. The report can be populated by looking up the combined names within the new lookup range, as shown below:


This same approach can be used when two, three, or more lookup columns need to be considered.


In addition to being able to perform multi-column lookups when the return value is numeric, the SUMIFS function has additional benefits when compared to traditional lookup functions. For example, it returns zero when no matching value is found, it returns the sum of all matches, it supports comparison operators, and it won’t break when a new column is inserted between the lookup and return columns.

So, when you are about to bust out the VLOOKUP function to do a lookup task, consider using SUMIFS instead. Believe it or not, the SUMIFS function makes a wonderful lookup function.

If you have any other preferred approaches to multi-column lookups, we’d love to hear more…please post a comment below.

Sample File

If you want to play with the workbook used to generate the screenshots above, please feel free to download the sample file:


Additional Resources

This post is a summary adaption of Excel University Volume 2, Chapter 15 Perform Lookups with SUMIFS. More information available:


This article was written by jefflenning


  1. Natanya

    Really helpful, Thanks!

  2. Vijay

    Thanks a ton.. Very simple and easy to use solution for both numbers and text strings! It helped me solve a problem in minutes Great job guys…

  3. Amy

    My only issue with this is when one of the columns has more than one word. I can’t include any spaces.

    Customers Table
    A2 (Lookup): CompanyCanadaVancouver
    B2 (Account): 1234
    C2 (Company): CompanyCanada
    D2 (Branch): Vancouver

    Contacts Table
    A2 (Account): =VLOOKUP([Company]&[Branch],Customers,2,TRUE)
    B2 (Company): CompanyCanada
    C2 (Branch): Vancouver

    It would be nice if I could do this with a space between Company & Canada.

    1. jefflenning Post author


      Hmmm…I tried it here with a space between Company & Canada, and it seemed to work. Here are the values I used in my tests:

      Customers Table
      A2 (Lookup): Company CanadaVancouver
      B2 (Account): 1234
      C2 (Company): Company Canada
      D2 (Branch): Vancouver

      Contacts Table
      A2 (Account): =VLOOKUP([Company]&[Branch],Customers,2,TRUE)
      B2 (Company): Company Canada
      C2 (Branch): Vancouver

      The VLOOKUP function returned the 1234 value successfully. I’m thinking perhaps it may be a different issue? For example, a common issue is that the export may actually include trailing spaces. If so, you can use TRIM to remove excess spaces. Another idea is that you can concatenate a space into the VLOOKUP function if needed, such as VLOOKUP(A1&” “&A2…).

      Also, I’d want to confirm that the values in both tables include, or exclude, the space. For example, that C2 in the Customers Table has a space (Company Canada) as well as the corresponding value in the Contacts Table (B2, Company Canada).

      I hope these ideas are helpful!


  4. Marc Quinn

    It’s not exactly applicable here but I’ve found that ‘sumifs’ can be done without SUMIFS, so to speak.

    For Smith sales in NC – using an added fictional Sales column (H) and changing the States so that some are matching (Changing Willard Smith to NC)….


    You need Shift&Enter or {} to activate the formula.

    1. jefflenning Post author

      Thanks for sharing your array formula alternative!!

  5. Amanda

    How would i do it if i wanted to look up 3 columns?
    Example A is the convenate of b&c.

    1. jefflenning Post author

      If the item you want to return is numeric, then I recommend using SUMIFS and just add another criteria_range/criteria_value pair of arguments. If the item you want to return is a text string, then I recommend using concatenate to combine the lookup columns.
      The sample file has example formulas for reference.

  6. Wayne Marsland

    Fantastic – thank-you

  7. Amit

    Table No Start date End date Society Name
    1 1/7/2015 3/7/2015 ABCD
    2 2/7/2015 5/7/2015 PQRS
    1 5/7/2015 7/7/2015 ABCD
    1 7/7/2015 10/7/2015 XYZ
    HI ,

    My query is that if i have to develop a vlookup with multiple criteria such that if Table No is 2 and the satart date and End date lies between the mentioned dates then the society name should be pulled out.
    Can you please help!!

    1. jefflenning Post author

      Since the SUMIFS function is designed to return a number, and not a name such as the society name ABCD, we can’t use it directly…however…we can use it along with a helper column. Here is the basic idea. You create a new helper column in the data range, something like “RecordID” or “ID” which is a unique number for that row, such as 1, 2, 3, 4, and so on. Then, assuming that the conditions can only be met by a single row, you can use the SUMIFS function with multiple conditions to return the RecordID to a cell, say A1. Then, you can use a VLOOKUP that finds the RecordID stored in A1 in the lookup range to return the society name. You could even combine the SUMIFS and VLOOKUP in a single formula with something like this: =VLOOKUP(SUMIFS(sum_range, criteria_range1, criteria1, …), lookup_table, 5, 0)
      Hope this idea helps!

  8. Sujay Wadeyar

    Well, I couldn’t have dumped into a better website looking in search for the excel solution. Thanks a lot!!!

  9. Anuraag



  10. Tiago

    What if i have two columns of amount to sum? Any suggestion? the sum range doesn’t appear to work with multiple columns…

  11. Srinivas J

    Saved me a lot of time. superb

  12. Esteban Osorio

    Hello Mr. Lenning,

    This simple solution to the problem that I thought it had become a puzzle, is incredibly helpful. Now, I was wondering, just for aesthetics, could it be posible for your example to just show an item e.g., Design Income, only once in the drop down list, the same with all items in the drop down lists. Just once.

  13. Harry

    Perfect Thanks Sir.

  14. Sampath

    Hi Jeff,

    Could you please help me?

    I have a table and it’s got Hotel Name, Basis – Bed & Breakfast (BB) or Half Board (HB) and a Price list on two columns with 1st column with the Single Rate Prices and Then 2nd column with Double Rate Prices. I need to calculate using the drop down lists 1st the Hotel Name then the Basis and then by Single or Double. Can I use “IF” formula and “VLOOKUP” formula for this? As i’m getting a error on the formula i wrote. could you please help me to write the code. here is the formula i wrote…. ‘=VLOOKUP(A10,Table1,IF(Table1[[#Headers],[Single Room Rate]]=”Single”,Table1[Single Room Rate],Table1[Double Room Rate]),1)


    1. Kurt LeBlanc

      Hey Sampath,

      hmm…I’m having trouble picturing your table…Could you copy it into a comment or email it to kurleb10@gmail.com? I’m happy to help if you do!

      Kurt LeBlanc

  15. Sandeep EVJ

    It is very Helpful. But can you help me.

    1) if 2 Vlookkup conditions’ Colum Ref. is…. Number & Number…………IT IS HELPFUL.
    1) if 2 Vlookkup conditions’ Colum Ref. is…. TEXT & TEXT…………IT IS HELPFUL.

    . But I can’t use the formula if Volookup …if …One Condition is TEXT,… and another Condition is DATE.
    Please Help me.

  16. Bill

    Why did they create a function to sum based on two or more criteria but no function to lookup based on two or more criteria?

    1. Kurt LeBlanc

      Hey Bill

      I can’t answer as to why Microsoft didn’t, but they may be developing it as we speak. They make improvements all the time.

      I can’t wait to see what they do next!
      Kurt LeBlanc

  17. Salim

    Can we loop vlookup in a single cell, like to search 2 thing/ item in a single vlookup command

  18. sanam humayin

    Hello sir I have one question I will send you excel sheet kindly send me your email address

    1. Kurt LeBlanc

      Hey Sanum

      I can help you on here, but I don’t want to give out my email.

      Thank you
      Kurt LeBlanc

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.