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

[richanor]

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

=VLOOKUP(C5,C5:C8,-1,FALSE)

would work. Any help would be greatly appreciated,

Rich

 

[AlphaFrog]

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

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

 

[tc200505]

The formula below should do the trick:

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

 

[richanor]

excellent - thankyou both.

Rich