How to lookup to the left

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

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