![]() ![]() This column stores the project IDs that are assigned to the corresponding employees present in Column D. We’ve added an extra column in the middle of the table. VLOOKUP to Return Multiple Values Based on Criteria Read More: Excel VLOOKUP to Return Multiple Values Verticallyģ. While auto-filling the cells horizontally, the formula will follow the sequence number of the SMALL function to extract data. The only major difference is that we’re using the COLUMNS function here to specify the sequence number of the SMALL function. The formula inserted here is almost similar to the first lengthy formula used in the previous example of the article where the extracted data had to be displayed vertically. And you’ll get all the names from the Manufacture department horizontally. Now, use Fill Handle and drag the Cell C16 rightward until you find the first #NUM error. Press Enter and you’ll find the first name of the employee from the specified department. So, the required formula in the output Cell C16 will be: And to use this combined formula, you must be an Excel 365 user. The TRANSPOSE function converts a vertical range of cells to a horizontal range or vice versa. If you want to see the names of the employees horizontally then you have to combine the FILTER function with the TRANSPOSE function. VLOOKUP and Return All Matches in a Row in Excel Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)Ģ. While using Fill Handle to fill down the rest of the cells, the formula uses this k-th number to extract data followed by the SMALL function. The ROWS function in this formula defines the k-th number for the SMALL function.Finally, the INDEX function shows the name of the employee based on the specified row number.The SMALL function in the formula pulls out the first small number found in the previous step and assigns this number to the second argument (row_number) of the INDEX function.IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”):With the help of IF function, this part of the formula returns the index number of the rows that meet the specified condition.So, this part of the formula returns an array of: ![]() MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)): MATCH function here converts the extracted row numbers starting from 1.ROW($B$5:$B$13): The ROW function extracts the row numbers of the defined cell references and returns the following array:.=INDEX($C$5:$C$13, SMALL(IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""),ROWS($A$1:A1)))Īfter pressing Enter, you’ll find the first name of the employee in the output Cell C16.īy using Fill Handle from Cell C16 to downward, you’ll get the rest of the names of the employees from the specified department at once.
0 Comments
Leave a Reply. |