But what if there are several matches in your lookup array and you want to get the 2 nd or 3 rd instance? The task sounds quite intricate, but the solution does exist! Formula 1.
How to create vlookup in excel 2016 how to#
How to use VLOOKUP to get 2 nd, 3 rd or n th matchĪs you already know, Excel VLOOKUP can fetch only one matching value, more precisely, it returns the first found match. INDEX MATCH formula with multiple criteria.
In Excel 365 that supports dynamic arrays it also works as a regular formula.įor the detailed explanation of the formulas, please see: In all versions except Excel 365, INDEX MATCH should be entered as an CSE array formula by pressing Ctrl + Shift + Enter.
So, add a column to the left of your table and copy the below formula across that column. It is important that the helper column should be the leftmost column in the table array because it's where Excel VLOOKUP always searches for the lookup value. To overcome this, you can add a helper column and concatenate the values from two lookup columns ( Customer and Product) there. A complicating factor is that each customer ordered multiple products, as shown in the table below:Ī usual VLOOKUP formula won't work in this situation because it returns the first found match based on a single lookup value that you specify. Suppose you have a list of orders and want to find the quantity based on 2 criteria, Customer name and Product. But what if you want to look up with several conditions? There are a few different solutions for you to choose from. However, it lacks an important feature - its syntax allows for just one lookup value. The Excel VLOOKUP function is really helpful when it comes to searching across a database for a certain value. VLOOKUP and INDIRECT to pull data from multiple sheets.How to Vlookup multiple criteria in Excel.Now that everyone is on the same page, let's take a closer look at the advanced VLOOKUP formula examples: VLOOKUP(lookup_value, table_array, col_index_num, )