Skip main navigation

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

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education