How to return Multiple Values Vertically Using VLOOKUP in excel

How to return Multiple Values Vertically Using VLOOKUP in excel

  • Post author:
  • Post category:Vlookup

VLOOKUP function returns a corresponding value of the first instance in a lookup value. The function searches specific value defined and returns the matching value in another column. The VLOOKUP function, just as the name looks up a value in the first column of a specified range of cells and then returns the results on the same row from another column.

The data in the table is vertically arranged with data in the rows. When information is arranged vertically with columns on the left, data can easily be retrieved by matching rows with columns on the left.

To return multiple values, several functions are combined together to form an array formula.

Syntax

=(INDEX(return_range, SMALL(IF(lookup_value = lookup_range , ROW(lookup_range) – MIN(ROW(lookup_range ))+1,””), ROW()))

Parameters

Index() – the function returns array element based on row and column numbers you specify.

Small() – Determines the kth smallest value in the array to be returned to the specified cell.

IF()- this function evaluates the condition and returns a value if the condition is met and another if the condition is false.

Min – returns the smallest number in a set of values.

Row – returns the row number

Column – returns the column number

Example: Returning values vertically

Vertically Using VLOOKUPscreenshot

From the above table, use vlookup to display the price of apples vertically.

Step1.
Type the following formula

=INDEX($D$2:$D$11,SMALL(IF($B$13=$B$2:$B$11,ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1,””),ROW(A1)))

Press shift+ctrl+enter to input the formula

Vertically Using VLOOKUPscreenshot

To add a reference to the cells press f4 key on your keyboard

Step 2: Once you type the formula, copy the formula in the cell c13 and paste it below or use the Autofill feature to copy the formula to the remaining cells until the #NUM! Appears.

Step 3: Delete the #NUM from the table

Vertically Using VLOOKUPscreenshot

In the above formula, D2: D11 is the column range in which you want to return the price values. B2:B11 is the range with the lookup value. B13 is the lookup value. A1 is the first cell of your table data range.

To trap any errors in the formula copied to other cells use IFERROR () function which is inserted at the beginning of your formula.

=IFERROR(INDEX($D$2:$D$11,SMALL(IF($B$13=$B$2:$B$11,ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1,””),ROW(A1)))