So far, we showed and you are probably aware of the two cool functions- **VLOOKUP** and **INDEX** (in a combination with the **MATCH function**) that are used as lookup options in Excel.

However, these functions are only used for the first instance of the lookup value. But what if we want to find the second, third, or any other lookup value?

For these cases, we need to use other functions and formulas. We will show two ways to do this in the examples below.

## Lookup the Nth Value Using Another Column

For our example, we will use the list of students’ names and the classes that they are taking:

First thing first, if you want to find the unique names in our first column, you can use the **UNIQUE function**:

1 |
=UNIQUE(A2:A19) |

As in the picture below:

**The **UNIQUE function recognizes unique values in an array and returns them. It is an array function added to the **Office 365 version** of Excel.

We will add columns for classes next to **column D**, and will use a **VLOOKUP formula** to see if we will get the needed results:

Of course, the **VLOOKUP** **function** always returns the first value, i.e. **Biology for Anthony**.

To change this, we will add a column between **column A** and **column B**, and we will insert the following formula:

1 |
=A2&COUNTIF($A$2:$A2,A2) |

We will drag this formula till the end of our table, and we will have the following results:

The **COUNTIF formula** in **column B** adds a number to the name of every employee to make it unique. First Jack that it finds becomes **Jack1**, the second becomes **Jack2**, and so on.

Now we have unique values that are related to values in **column A**.

To find the classes for every student, we need to add the following formula in **cell F2** (the first cell in which we want to find the class):

1 |
=IFNA(VLOOKUP($E2&COLUMNS($F$1:F1),$B$2:$C$19,2,0),"") |

We will input this formula into our table, and have the following results:

What changed for this **VLOOKUP**? Well, now it has unique names that can help out when finding the matching course.

Our lookup value is $E2&COLUMNS($F$1:F1). It adds the number to the employee name based on the number of the column. When we use this formula for Kevin (**cell F4**) the lookup value is **“Kevin1”**. The next lookup value in the same row will be **“Kevin2” **and so on.

## Lookup the Nth Value Using Array Formula

If you do not want to be in a situation to add a new column all the time, you can use a different approach. We will copy and paste the original table to the new sheet. We will not, however, add another column.

The whole preparation for the new formula looks like this:

In **cell E2** we will input the following formula:

1 |
=IFERROR(INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19)-1,""),COLUMNS($E$1:E1))),"") |

The above is the array formula. If you are using an older version of Excel, to make this formula work you need to insert it with the combination of **CTRL + SHIFT + ENTER**. Simple **ENTER** will not do the trick.

However, array formulas are integrated into new Excel versions **(2019 and Office 365**) so if you are working in these environments, you will not need the above-stated combination. Input **ENTER **and go for it.

So how does exactly the above formula work?

The first important part of the formula is:

1 |
$A$2:$A$19=$D2 |

This part observes every cell in **range A2:A19** with the value in **cell D2**. The value in **cell D2** is “Anthony” so this formula checks if this value is found in the **range A2:A19** or not.

It goes through every cell in this range and returns **TRUE and FALSE** values.

For the next part, we have:

1 |
IF($A$2:$A$19=$D2,ROW($A$2:$A$19)-1,"" |

This formula uses the **TRUE and FALSE** values that we found above and replaces the** TRUE** value with the position of this value in our range, and **FALSE** with blanks.

Then we have the **SMALL function** in our formula:

1 |
SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19)-1,""),COLUMNS($E$1:E1))),"") |

This formula picks the smallest numbers from our array. It uses the **COLUMN function** to successfully show column numbers.

Now we add the **INDEX formula** to the mix. This function returns a value from **column B** having in mind the position that is returned by the **SMALL function**. In **cell E2**, it will return **„Biology“,** as it Is the first item found in **range B2:B19**. Then, in **cell F2**, it will find **„Physics“**, and so on.

1 |
INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19)-1,""),COLUMNS($E$1:E1))),"") |

With everything said above, our table looks like this: