lookup and offset combination

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I am trying to return the value of a cell that is offset from a lookup reference cell. My data looks something like this (if it shows ok)

A B C
1 name 50 65
2 100 3
3 40 2

Cells A2 and A3 are empty. If I always want to return the value 2 columns over and 2 rows down from the lookup "name" (which happens to be in cell A1, but may not always be in that cell), what formula combination do I use? I have tried nesting lookup in offset, but I get an error message. Here is how I wrote it

=offset(LOOKUP(e50,A1:A500,b1:b500),2,1)
e50 contains "name
The lookup returns the value in B1, and I was hoping by nesting it in offset I could get the value in C20 which is 2 rows down and 1 column over from B1

Any thoughst? Thanks.
 
How about:

=INDEX(C1:C500, MATCH(E50, A1:A500, 0) + 2)

--
Regards,

Juan Pablo González

nathan said:
Hello,

I am trying to return the value of a cell that is offset from a lookup
Click to expand...
reference cell. My data looks something like this (if it shows ok):
A B C
1 name 50 65
2 100 30
3 40 20

Cells A2 and A3 are empty. If I always want to return the value 2 columns
Click to expand...
over and 2 rows down from the lookup "name" (which happens to be in cell A1,
but may not always be in that cell), what formula combination do I use? I
have tried nesting lookup in offset, but I get an error message. Here is
how I wrote it:
=offset(LOOKUP(e50,A1:A500,b1:b500),2,1))
e50 contains "name"
The lookup returns the value in B1, and I was hoping by nesting it in
Click to expand...
offset I could get the value in C20 which is 2 rows down and 1 column over
from B1.
 
Back
Top