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 (i.e. the value that we are looking for), should be located in the first column of the table array.

If this condition is not met properly, it returns the #N/A error. In such cases you can fix your formula by tweaking the table array, to ensure the lookup column is at the far left. But in the case of large data tables, it might be impractical.

In this article I’ll share 5 ways to lookup vertically when the lookup value is not in the first column of the table array.

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

Let’s examine the problem by looking at this example:

In this example, we supply a name in cell F4. The VLOOKUP formula then looks for this name in the table (in range A2:C10), and brings back that person’s salary from column 3 (i.e. column C).

• The value we are looking for (i.e. the “lookup value”) is Trevor Rios, supplied in cell F4.
• The area we are looking in (i.e. the “table array”) is $A$2:$C$10.
• The information we are looking for is Trevor’s salary, so the column index number is 3 (being the third column in the array).

But here, the lookup value (Trevor Rios) is located in the second column of the table array (i.e. in column B). As a result, the VLOOKUP function can’t find our lookup value in the first column, and 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 in the first column of the table array. But in the first column of the table array $A$2:$C$10 there’s only Employee IDs, and no names. Thus, the VLOOKUP function can’t find Trevor Rios. So it leaves an #N/A error.

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

Additional drawbacks and limitations of the VLOOKUP Function

• As we’ve seen in the example above, the lookup value has to be in the leftmost column
• A VLOOKUP also can’t look up to the left. E.g. we can’t look for a name in column B and ask it to bring back the Employee ID in column A.
• The column index number must be specified in the formula. This can become challenging when the data table has a large number of columns. Though the column count can be managed using another function.
• When the lookup column is not sorted alphabetically, the VLOOKUP function may fail to extract the intended output in some cases.

Let’s now look at 5 ways to perform a lookup in Excel when the lookup value is NOT in the first column.

Method #1: Replacing VLOOKUP With a Combination of INDEX & MATCH (best method!)

Instead of using the VLOOKUP function, you can use a combination of the INDEX & MATCH functions.

With this method, the lookup value can be in any column. You can even look backwards, e.g. look for a name in column B, and bring back the Employee ID for that name in column A!

Firstly, let’s see how the INDEX and MATCH functions work

In its simplest form, INDEX fetches a value from a specified position within a range.

E.g. in the image below, the formula in cell B12 tells Excel to look up the item that is located in position 6 within the range B2 to B10:

=INDEX(B2:B10, 6)

If you count from the top, you will see that the 6th item is “Trevor Rios”.

But instead of counting manually, we will use the MATCH function to do the counting:

=MATCH("Trevor Rios", B2:B10, 0)

The formula above will tell us that the position of Trevor Rios is 6th in the range. As we confirmed when we counted manually, we found his name in position 6. I just used the text string here for clarity, but we could instead point to a cell containing the name, as follows:

=MATCH(F4, B2:B10, 0)

If you haven’t downloaded the example file yet, you can do so now:

Now we combine the two formulas, and tell the INDEX formula to bring back the value in the cell in column C that lines up with the item in the same position, in column B:

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

• So in the image above, the formula in cell F14 performs a lookup in column C (Salary) using INDEX.
• The MATCH function then finds the position of the name provided in cell F3 (Trevor Rios), in column B.
• The result (6) is fed back into the INDEX function and it brings back the 6th value, being $15,000.

In A Nutshell

Formula Syntax

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

The Formula

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

• Here, F3 is the lookup value, i.e. the name Trevor Rios.
• B2:B10 is the lookup array for the MATCH function, i.e. the column that contains his name.
• 0 tells Excel that we need an exact match between the lookup value and the items in the lookup array. So if we can’t find Trevor Rios, don’t bring back the next best thing, rather tell us that the name is not listed by bringing back a #N/A error.
• Putting it all together, MATCH(F4, B2:B10, 0) says: Look in the range B2 to B10 and bring back the position of the value in F4 (i.e. Trevor Rios). In this case, it finds Trevor Rios in position 6, i.e. the sixth item down.
• C2:C10 is the array for the INDEX, i.e. the column that contains the answer we are looking up.

Method 2: Use XLOOKUP When Lookup Value Is Not in the First Column

The XLOOKUP function is available only in Excel for Microsoft 365 and Excel 2019 onwards.

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.

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

The easiest method is to modify our lookup table (i.e. the “table array”) in order for the lookup column to become the first column of the table array. So instead of looking in the entire table from column A through to column C, we change the table to be from column B to column C.

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. Excel will now look for the lookup value in B2:B10, instead of A2:A10, as column B is now the first column of the table array.

Just remember that the size of our table has changed, so our “column index number” is 2, and not 3. We’ve removed column A from our table array, so column B is now the first column, and column C is the second column.

Although one could argue that this is the simplest solution, we don’t always have the luxury of manually changing formulas to make sure our lookup column comes first. That’s why the INDEX & MATCH method above is Numero Uno… by far the best and most versatile way.

Method #4: Vertical lookup 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 the 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.

Method #5: 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

We’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!