The **VLOOKUP (Vertical Lookup)** function is one of the powerful Excel functions that is used widely by many people every day. It is normally used to look for a single matching value in the first column of a table and return a single value in the same row from a different specified column.

However, in combination with **COUNTIF, INDIRECT,** and **ROW** functions, the **VLOOKUP** function can be used to return multiple values vertically.

In this tutorial, we will demonstrate how this can be achieved.

We will use the following dataset to explain and demonstrate how we can use the **VLOOKUP **function to look for a particular matching value and then return all the values based on that particular value.

You can reproduce the dataset in an Excel file exactly as it appears below so that you follow the tutorial.

**Steps to Vertically Returning Multiple Values in Excel using the VLOOKUP Function**

**Step 1** – Move to **Column B** which we shall make a helper column.

**Step 2** – Enter the formula **=C3&COUNTIF(C3:$C$14,C3) **in cell **B3 **as follows:

When you press the Enter key, the following result will appear:

**Step 3**– Click on the **Fill Handle** and drag it down to the last cell. You will then see a list of **Product Types** with their serial numbers, for example, **Desktop Computer1**, **Desktop Computer2**:

**Step 4** – Move to **Column G** and key in the name of the **Product Type** that you want to look up as the **Column Header**.

In the following example, I have keyed in **IP Camera** in cell **G2**:

**Step 4** – Enter the formula **=VLOOKUP(G2&ROW(A1:INDIRECT(“A”&COUNTIF(C3:C14,G2))),B3:E14,3,FALSE)** in **cell F3 ** and then press the **Enter** key if you are using **Office 365**. If not, select many cells first before entering the formula, and once you have entered the formula press **CTRL+SHIFT+ENTER**.

You will see the following multiple values which are the names of IP Cameras:

When you change the lookup value in **cell G2**, you will get different values in the column. For example, if you change the lookup value to **Laptop Computer**, you will get a list of the names of laptop computers in the dataset.

**Explanation of the formula **

**=VLOOKUP(G2&ROW(A1:INDIRECT(“A”&COUNTIF(C3:C14,G2))),B3:E14,3,FALSE)**

**G2 **is the cell that has the lookup value, which in this case is **IP Camera,** and can be changed to any of the other **Product Types **depending on which **Product Names** you want to be returned.

**Range C3:C14** is the lookup range and the first column of the dataset.

**Range B3:E14** is the table array together with the helper **column B**.

3 is the column index number, representing the column that has the data we want to be returned, in this case, **Product Name**.

The **COUNTIF **function is innermost in the formula and it computes how many cells in the lookup range **C3:C4 **contain the lookup value in **cell G2**. In this case, it returns the value of 4.

The value 4 is passed to the I**NDIRECT** function leading to read **INDIRECT(“A”&4)**, yielding the cell reference **A4**.

The **ROW **function now gets the argument of range **A1:A4 **and becomes **ROW(A1:A4)**.

The **ROW **function returns the array **{1,2,3,4}.**

The value in cell **G2** is then concatenated with the array returned by the **ROW** function, resulting in another array **{IP Camera1, IP Camera2, IP Camera3, IP Camera4}**.

The formula **=VLOOKUP(G2&ROW(A1:INDIRECT(“A”&COUNTIF(C3:C14,G2))),B3:E14,3,FALSE) **now becomes **=VLOOKUP({IP Camera1, IP Camera2, IP Camera3, IP Camera4},B3:E14,3,FALSE).**

Each value of the array **{IP Camera1, IP Camera2, IP Camera3, IP Camera4} **is looked for in the lookup **column B** and the corresponding **Product Name **in **Column 3** is returned and we get a list of the** Product Names**.

**Conclusion**

In this tutorial, we have shown how the **VLOOKUP** function together with the **COUNTIF, INDIRECT**, and **ROW** functions can be used to extract multiple values vertically from a dataset.