{"id":29248,"date":"2023-05-29T16:08:08","date_gmt":"2023-05-29T14:08:08","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=29248"},"modified":"2024-06-06T20:49:48","modified_gmt":"2024-06-06T18:49:48","slug":"vlookup-with-multiple-criteria-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/vlookup-with-multiple-criteria-in-excel\/","title":{"rendered":"Mastering VLOOKUP with Multiple Criteria in Excel – 3 Quick and Easy Methods"},"content":{"rendered":"

\"Mastering<\/a>VLOOKUP is used to search for a value in the first column of a table array and return a value in the same row from another column<\/strong>. But what happens when we want to search for a value based on multiple criteria<\/strong>? VLOOKUP with multiple criteria has got that covered!<\/em><\/p>\n

Before we proceed further, let us quickly go through the syntax of VLOOKUP<\/strong> –<\/p>\n

Formula breakdown:<\/span><\/strong><\/em>
\n=VLOOKUP(lookup_value,\u00a0table_array,\u00a0col_index_num,\u00a0[range_lookup])<\/p>\n

What it means:<\/span><\/strong><\/em>
\n=VLOOKUP(this value,\u00a0in this list,\u00a0and get me value in this column,\u00a0Exact Match\/FALSE\/0])<\/p>\n

The syntax of VLOOKUP is such that it cannot natively search multiple criteria<\/strong>. But we can make a few tweaks<\/strong> to the functions and<\/a> use VLOOKUP with multiple criteria to effortlessly find data and streamline the data analysis process<\/strong> in Excel. In this article, we will be covering 3 quick and easy methods<\/a><\/strong> of using VLOOKUP with multiple criteria in Excel –<\/p>\n

Let us look at each one of these methods in detail!<\/p>\n

Download the Excel Workbook below to follow along and understand how to use VLOOKUP with Multiple Criteria in Excel \u2013<\/strong><\/h4>\n

download excel workbook<\/strong><\/span>VLOOKUP-with-Multiple-Criteria.xlsx<\/span><\/a><\/h4>\n
\n
\n

 <\/p>\n<\/div>\n<\/div>\n

Method 1: Helper Column<\/span><\/b><\/p>\n

We can create a new column that concatenates the two columns<\/strong> containing the criteria. It is important to make sure that this column is at the beginning of the table<\/strong> as Excel will search the left-most column<\/strong> of the table array.<\/p>\n

Suppose we have a dataset<\/strong> containing information related to sales representatives, region, and sales amount. We need to use VLOOKUP to match two criteria –<\/p>\n