Skip main navigation

Hurry, only 2 days left to get one year of Unlimited learning for £249.99 £174.99. New subscribers only. T&Cs apply

Find out more

How to lookup to the left

In this article, Paula Guilfoyle will show you how to overcome one of the limitations of VLOOKUP, Looking up to the left.
EYE
© paula guilfoyle

How can I lookup to the left?

One of the limitations posed by VLOOKUP is that it can only look up to the right. But what if you want to look up to the left? Overcoming this problem is not as tricky as you might think, as you have already been equipped with the formulas needed.

Let’s take a further look at INDEX

INDEX=(array, row number, [column number])

 

You may have noticed that the column number has been placed between []. The reason for this is because the column number can be omitted if necessary.

 

It is also worth noting that an array does not have to be multiple rows and columns. It can contain a single row or a single column of data

 

Excel spreadsheet showing two tables of data

 

Looking at the image, in the top table, the Stock Code is found on the right of the table. Beneath this, we have a table with the Stock Codes and we want to look up the Product from the table above

 

In the lookup table, the Products are found in cells A1:A5. This is a single row and can be used as an array.

 

When you use a single row or column in an array with INDEX, the intercepting point can only be in that selected row or column. We can now set A1:A5 as the array in an INDEX function

 

To deal with the row number we can use MATCH

 

=MATCH(A9,C1:C5,0)

 

which would look to cell A9 (RB01) and will find its position in the selected range. In this case, it would return 2.

 

Knowing this, we can put the following formula together

 

 =INDEX(A1:A5,MATCH(A9,C1:C5,0))

Wherewith INDEX we are looking for an intercepting point in the Products column, and for the row number we are looking up the stock code using MATCH to return the relative row number

© paula guilfoyle
This article is from the free online

Excel Skills to Make an Impression

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now