VLOOKUP function returns a corresponding value of the first instance in a lookup value. The function searches specific values 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 elements 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
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
To add a reference to the cells press the f4 key on your keyboard
Step 2: Once you type the formula, copy the formula in 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
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)))