vlookup problem

[Alfredo_CPA]

I have a simple vlookup function that works fine.

Now I need excel to give me the value wich is in the intersection of 5 rows
down and 13 clolumns to the left of the cell wich contains the output of the
vlookup result. (i.e. if the value of the output of the Vlookup is in R39,
now I need a formula that gives me as result the value in E44)

Any idea of how to perform this?

Thanks

 

[T. Valko]

Maybe this:

=OFFSET(R39,5,-13)

 

[Alfredo_CPA]

R39 is a variable, is not always R39, rather than refering to a constant
cell, I need to refer to "the cell that contains the result of my Vlookup",
which could be whatever row in column "R")

 

[T. Valko]

Will the result of the lookup formula be the *only* entry in column R?

Once we have the "rules" on how to find the variable cell there should be no
problem getting the result you're looking for.

I'm also assuming that the offset, 5 rows, -13 columns is *relative* to the
variable cell?

 

[Bernard Liengme]

I think we could do better with more info. But here goes.
Let R36:Z99 hold a table. You want to look up a value in column Q and return
a value from a column in the table
In R36:Q40 I have: a, b, c, d,
In Q36:Q40 I have some text; apple, pear, plum, orange
In S36:S40 I have text: dog, cat, horse, donkey

Let A1 have the value 2 - meaning you want to return a value from the 2
column in
Let A2 have the value to be looked up, say "e"
The formula =VLOOKUP(A2,Q35:Z40,A1) returns "cherry"
The formula =MATCH(A2,Q35:Q40,A1) returns 5 tells me that "e" is in row five
of the array Q35:Q40
The formula =INDEX(Q35:Z40,MATCH(A2,Q35:Q40,0),A1) returns "cherry" just
like the VLOOPUP
The formula =INDEX(A35:Z40,MATCH(A2,Q35:Q40,A1),16+A1-13) returns the value
in row 5 and column E of the array A35:Z40

best wishes

 

[Alfredo_CPA]

Thanks Bernard, that does the job....I really apprecciate your help!!!

 

[Alfredo_CPA]

Thanks for your input. I was able to figure it out with Bernard posting

 

[T. Valko]

Ok, good deal!

--
Biff
Microsoft Excel MVP

Thanks for your input. I was able to figure it out with Bernard posting