IF compares the lookup value (D2) with each value in the lookup range (A3:A13), and if the match if found, returns the relative position of the row an empty string ("") otherwise. Working from the inside out, here's what you do:Īt the core of the formula, you use the IF function to get the positions of all occurrences of the lookup value in the lookup range: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"") This is an example of intermediate to advanced uses of Excel that implies basic knowledge of array formulas and Excel functions. The result will look something similar to this: For this, select the range of cells where you've just entered the formula, and drag the fill handle (a small square at the lower right-hand corner of the selected range) to the right. =IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")Īs you see, the 1 st formula is a bit more compact, but the 2 nd one is more universal and requires fewer modifications (we will elaborate more on the syntax and logic a bit further). Or, you can enter the formula in the first cell, hit Ctrl + Shift + Enter, and then drag the formula down to a few more cells (in this case, you will be able to edit the formula in each cell individually). Under the first name, select a number of empty cells that is equal to or greater than the maximum number of possible matches, enter one of the following array formulas in the formula bar, and press Ctrl + Shift + Enter to complete it (in this case, you will be able to edit the formula only in the entire range where it's entered).To quickly get all different names in a list, you can use a formula to extract distinct values in Excel. In this example, the names are input in cells D2:G2: Enter a list of unique names in some empty row, in the same or another worksheet.To have it done, please follow these steps: Your goal is to get a list of all products sold by a given person. Let's say, you have the seller names in column A and the products they sold in column B, column A containing a few occurrences of each seller. Vlookup multiple matches and return results in a column INDEX - returns an array element based on the row and column numbers you specify.īelow you will find a few examples of such formulas.SMALL - gets the k-th smallest value in the array.IF - evaluates the condition and returns one value if the condition is met, and another value if the condition is not met.The task can be accomplished by using the following functions in an array formula:
#How to use vlookup in excel for names how to
How to do multiple Vlookup in Excel using a formulaĪs mentioned in the beginning of this tutorial, there is no way to make Excel VLOOKUP return multiple values.