Since the relative reference changes based on a position of the cell where the formula is copied, in row 3 it will become $B$2:B3, in row 4 - $B$2:B4, and so on.Ĭoncatenated with the customer name (B2), the formula takes this form: To get the occurrence, use the COUNTIF function with a mixed range reference (the first reference is absolute and the second is relative like $B$2:B2). But this time, we will populate it with customer names and occurrence numbers like " John Doe1", " John Doe2", etc. The simplest way is to add a helper column to the left of the table like we did in the first example. Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2 nd or 3 rd product bought by a given customer. 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 use vlookup in excel 2013 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.
Firstly, a lookup value is limited to 255 characters, and secondly, the worksheet's design may not allow adding a helper column. In theory, you can use the above approach to Vlookup more than two criteria. For example, we used a space character to separate the criteria in both the helper column (B2&" "&C2) and VLOOKUP formula (G1&" "&G2). For the formula to work correctly, the values in the helper column should be concatenated exactly the same way as in the lookup_value argument. =VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)Īlternatively, create a named range for the lookup table (say, Orders) to make the formula easier-to-read:įor more information, please see How to Vlookup from another sheet in Excel. In case your lookup table is in another sheet, include the sheet's name in your VLOOKUP formula. The range_lookup argument is set to FALSE to Vlookup an exact match. Or, input the criteria in separate cells (G1 and G2 in our case) and concatenate those cells:Īs we want to return a value from column D, which is fourth in the table array, we use 4 for col_index_num. =VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE) This will populate the helper column with the values from columns B and C (the space character is concatenated in between for better readability):Īnd then, use a standard VLOOKUP formula and place both criteria in the lookup_value argument, separated with a space: 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, )
0 kommentar(er)
