# Can you use a negative number in the column field of a VLOOKUP?

#### richanor

##### Active Member
Hi

I am trying to do a vlookup, but the value I want to return is to the left of the table array. I can see the obvious problem that a VLOOKUP looks for a match in the first column of the defined range - but I was wondering if there was any way around this?

In case I haven't explained this very well (or incase there is an alternative solution not using VLOOKUP), what I am trying to do is:

I want to look at the value in cell C5. If it is a "1", I want "TRA" to appear in cell AC5. If it is a "2", I want "DUO" to appear in cell AC5. If it is a "3" I want "AZA" to appear in AC5. If it is a "4", I want "SYS" to appear in AC5.

Currently, the TRA, DUO, AZA, SYS are in the range B5:B8, hence the question about whether

Code:
``=VLOOKUP(C5,C5:C8,-1,FALSE)``
would work. Any help would be greatly appreciated,

Rich

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### AlphaFrog

##### MrExcel MVP
Vlookup can't lookup to the left, but Index\Match can.

=INDEX(B5:B8,MATCH(C5,C5:C8,0))

Last edited:

#### tc200505

##### New Member
The formula below should do the trick:

=IF(C5=1,B5,IF(C5=2,B6,IF(C5=3,B7,IF(C5=4,B8))))

#### richanor

##### Active Member
excellent - thankyou both.

Rich

1,106,892
Messages
5,514,010
Members
408,981
Latest member
Slumsu