# 5 Ways to Vlookup When Lookup Value Is Not in the First Column

Generally, we use the **VLOOKUP **function to look up a certain value vertically across columns. One of the major conditions of using the **VLOOKUP **function is that the lookup value should belong to the first column of the table array. If this condition is not met properly, it returns the **#N/A** error. In such cases either you have to tweak the table array or just alter the lookup column. But in the case of large data tables, it might be impractical. Thus, I’m going to share **5 ways** to lookup vertically when the lookup value is not in the first column of the table array.

Table of Contents

## VLOOKUP Function Returns #N/A When Lookup Value Is Not in the First Column

In the following example,

- The lookup value is
**Trevor Rios**in cell**F4**. - The table array is
**$A$2:$C$10**.

Here, the lookup value is located in the second column of the table array. As a result, the **VLOOKUP **function throws the** #N/A** error in cell **F5**.

The **VLOOKUP **function is programmed in such a way that it always looks for the lookup value across the first column of the table array.

In the first column of the table array **$A$2:$C$10**, there’s no such record as **Trevor Rios** but employee ids. Thus, the **VLOOKUP **function can’t retrieve any data. So it leaves a **#N/A** error.

If you click on the error icon, it displays **‘A value is not available to the formula or function.’.**

### Odds of Using VLOOKUP Function

The **VLOOKUP **function has some limitations. They are:

- Either the output column range or its column index number must be specified in the formula. This becomes incredibly challenging when the data table has a large number of columns. Though the
**column count can be managed**using another function. - It pre-assumes that the lookup column is letterwise sorted.
- It creates a static formula. This means that after applying the
**VLOOKUP**function, a change in the source data doesn’t update the output. Which is depressing in some cases. - When the lookup column is not sorted, the
**VLOOKUP**function may fail to extract the intended output in some cases.

### Alternatives to the VLOOKUP Function

If you have issues with the limitations of the **VLOOKUP **function stated above, you can move on to the **INDEX **& **MATCH **functions. These two functions in collaboration provide a lot of flexibility over the **VLOOKUP **function.

But, if the limitations are not that important, you can definitely use the **VLOOKUP **function.

## Best Way to Vlookup When Lookup Value Is Not in the First Column

Instead of using the **VLOOKUP **function, you can use the **INDEX **& **MATCH **functions to look up vertically across columns.

It doesn’t require to be the lookup value in the first column of the lookup array.

All you need to do is, insert the output array inside the **INDEX **function. Then insert the lookup value, lookup array, and the match type inside the **MATCH **function.

**Formula Syntax**

**=INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])**

**The Formula**

**=INDEX(C2:C10,MATCH(F4,B2:B10,0))**

**Formula Argument Explanation**

- Here,
**F4**is the lookup value. **B2:B10**is the lookup array.**0**refers to an exact match between the lookup value and the lookup array.**MATCH(F4,B2:B10,0)**looks for an exact match between**F4**and the lookup array**B2:B10.****C2:C10**is the output array.

### Alternative Way #1: Vlookup with CHOOSE & VLOOKUP Functions When Lookup Value Is Not in the First Column

The **CHOOSE **function allows us to choose data ranges from a table and create a virtual table inside a formula.

We can utilize this advantage of creating a virtual table to vlookup when the lookup value is not in the first column.

**CHOOSE Function Syntax**

Let me show you how the **CHOOSE **function works.

**Formula**

=CHOOSE({1,2},B2:B10,C2:C10)

**Usage Guide**

Just insert the above formula in a blank cell.

This is an array formula.

So press **ENTER **to insert if you are using **Excel for Microsoft 365.**

Otherwise, press **CTRL + SHIFT + ENTER** to insert the formula in a blank cell.

**How CHOOSE Function Works?**

I’ve inserted two different column ranges, **B2:B10** and **C2:C10 **inside the **CHOOSE **function.

Before the column ranges, I have mentioned the index number as** {1,2}.**

This means the **CHOOSE **function creates a two-column table using the ranges **B2:B10** and **C2:C10**. The index number **{1,2}** makes the first range, **B2:B10;** the first column of the newly created table. Then it makes the second range, **C2:C10;** the second column of the table.

If you alter the index number as **{2,1},** it will make **B2:B10**, the second column, and **C2:C10 **the first column. Have a look.

**Using VLOOKUP & CHOOSE Functions Together**

Now we know how the **CHOOSE **function works.

So we can use the **CHOOSE **function inside the **VLOOKUP **function to create a virtual table of two columns.

In the first column, we will keep the lookup column. The second column will be the output column that we want to extract using the **VLOOKUP **function.

**Syntax of the Formula**

=VLOOKUP (lookup_value, CHOOSE(index_num, value1, [value2], ...), col_index_num, [range_lookup])

**Formula**

=VLOOKUP(F4,CHOOSE({1,2},B2:B10,C2:C10),2,FALSE)

**Explanation of the Formula**

In the above formula, the **CHOOSE **function creates a virtual table having two columns.

The first column is **B2:B10** which is the lookup column.

Then the second column, **C2:C10** is our desired column from where we want to pull data.

Thus, I’ve used** 2 **as the column index number which indicates column, **C2:C10.**

The argument **FALSE **refers to an exact match as usual.

Finally, **F4 **is the lookup value that looks for **Trevor Rios** in the lookup column, **B2:B10.**

### Alternative Way #2: Use XLOOKUP When Lookup Value Is Not in the First Column

The **XLOOKUP function** is available only in **Excel for Microsoft 365** and the later versions.

**XLOOKUP Function Syntax**

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

**The Formula**

=XLOOKUP(F4,B2:B10,C2:C10)

**Formula Argument Explanation**

**F4**is the lookup value.**B2:B10**is the lookup array.**C2:C10**is the return array.

### Alternative Way #3: Modify Table Array When Lookup Value Is Not in the First Column

If you still want to use the **VLOOKUP **function to lookup vertically, you must modify the table array. The modification should be in such a way that the lookup column becomes the first column of the table array.

**The Formula**

=VLOOKUP(F4,B2:C10,2,FALSE)

In this formula, instead of using **A2:C10** as the table array, I have used **B2:C10.** Here, **B2:C10** is the lookup array, which is the first column of the table array now.

In all cases, the table array modification may not be possible. In such cases, you better use the **INDEX **& **MATCH **combination.

### Alternative Way #4: Move Lookup Column to the First When Lookup Value Is Not in the First Column

If your data table is relatively small, then you can try to move the lookup column at the beginning of your data table.

Though this is impractical for a large data table having a lot of columns.

Anyways, here’s how to move the lookup column to the beginning of the dataset.

**Usage Guide**

**Step_1:** Select the column first that you want to move.

**Step_2:** Then place your cursor at the side border of the column header.

**Step_3:** Press and hold the **SHIFT **key on your keyboard.

**Step_4:** Now drag the column header to the left until it reaches the beginning of the data table.

**Step_5:** Now use the regular formula of the **VLOOKUP **function.

**The Formula**

=VLOOKUP(F4,A2:C10,3,FALSE)

**Formula Argument Explanation**

**F4**is the lookup value.**A2:C10**is the table array.**3**is the column index number.**FALSE**refers to an exact match between the lookup value,**F4**, and the table array**A2:C10.**

## Conclusion

I’ve discussed **5** different ways to lookup vertically across columns when the lookup value is not in the first column. I hope you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!