offset within vlookup

[Guest]

Long story short, i'm trying to define several vlookup formulas. The vlookup
works, but excel doesnt understand to lookup the cell in the same row, in
column E as the lookup value when trying to define.


undefined formula in cell K80:
VLOOKUP($E80,VLK!$F$4:$H$9,2,FALSE)

when defining this, somehow i need to tell the formula to look up the value
in the cell 6 rows to the left.

TIA!!!

 

[Peo Sjoblom]

6 Rows to the left, where is that? If you meant columns you can use

=INDEX(VLK!return_column,MATCH(E80,VLK!F4:F9,0))


--


Regards,


Peo Sjoblom

 

[Boris]

Long story short, i'm trying to define several vlookup formulas. The vlookup
works, but excel doesnt understand to lookup the cell in the same row, in
column E as the lookup value when trying to define.


undefined formula in cell K80:
VLOOKUP($E80,VLK!$F$4:$H$9,2,FALSE)

when defining this, somehow i need to tell the formula to look up the value
in the cell 6 rows to the left.

TIA!!!

Something like this?

=VLOOKUP(OFFSET(K80,0,-6),VLK!$F$4:$H$9,2,FALSE)

All you have is reference to K80 (the same cell the formula is in) and
offset.

Regards,

B.

 

[Peo Sjoblom]

But what would be the point of this?

1. By using OFFSET the formula becomes volatile and will slow down a large
workbook.

2. Why not use VLOOKUP(E80,etc

instead of offsetting K80?


--


Regards,


Peo Sjoblom

 

[Guest]

I need the offset not to be based on K80, but the cell that the formula is
in... kinda like the cell select in VBA.

 

[Guest]

I need the offset not to be based on K80, but the cell that the formula is
in... kinda like the cell select in VBA.

 

[Peo Sjoblom]

Maybe something like

=VLOOKUP(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),,-6),VLK!$F$4:$H$29,2,0)



Not tested more than in a few cells, note that if you are using -6 offset
than you will get a ref error if you put the formula in first 6 columns
counted from column A

--


Regards,


Peo Sjoblom