Vlookup and offset function?
 

I am trying to write a lookup function that will find a row of data for a
specific store (indicated by a store number, i.e. my "lookup value") then
return the data for the row immediately below the specified store's data. (I
also want to create a formula for the store data on the row immediately above)

It seems that the offset function should come into play, but I'm hitting a
wall on how to do this...any help?

Thanks

Vlookup and offset function?
 

Use MATCH to find the row and then OFFSET

--
Don Guillett
SalesAid Software

"David B" wrote in message
...
I am trying to write a lookup function that will find a row of data for a
specific store (indicated by a store number, i.e. my "lookup value") then
return the data for the row immediately below the specified store's data.
(I
also want to create a formula for the store data on the row immediately
above)

It seems that the offset function should come into play, but I'm hitting a
wall on how to do this...any help?

Thanks

Vlookup and offset function?
 

Don,

Thanks for the reply...

I get the Match function to work, but don't know how to use "offset" based
on the result I get from "Match"

"Don Guillett" wrote:

Use MATCH to find the row and then OFFSET

--
Don Guillett
SalesAid Software

"David B" wrote in message
...
I am trying to write a lookup function that will find a row of data for a
specific store (indicated by a store number, i.e. my "lookup value") then
return the data for the row immediately below the specified store's data.
(I
also want to create a formula for the store data on the row immediately
above)

It seems that the offset function should come into play, but I'm hitting a
wall on how to do this...any help?

Thanks

Vlookup and offset function?
 

If column B contains the value you want to retrieve and A contains the
store codes, how about:

=INDEX(B1:B100,MATCH(code,A1:A100,0)+1)

Does this help?
Kostis Vezerides

David B wrote:
I am trying to write a lookup function that will find a row of data for a
specific store (indicated by a store number, i.e. my "lookup value") then
return the data for the row immediately below the specified store's data. (I
also want to create a formula for the store data on the row immediately above)

It seems that the offset function should come into play, but I'm hitting a
wall on how to do this...any help?

Thanks

Vlookup and offset function?
 

This will get the cell value below the matched value in F1:K1

=OFFSET(INDEX(F1:K1,MATCH(value,F1:K1,0)),1,0)

You could also use

=INDEX(F2:K2,MATCH(value,F1:K1,0))

without OFFSET

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"David B" wrote in message
...
Don,

Thanks for the reply...

I get the Match function to work, but don't know how to use "offset" based
on the result I get from "Match"

"Don Guillett" wrote:

Use MATCH to find the row and then OFFSET

--
Don Guillett
SalesAid Software

"David B" wrote in message
...
I am trying to write a lookup function that will find a row of data for
a
specific store (indicated by a store number, i.e. my "lookup value")
then
return the data for the row immediately below the specified store's
data.
(I
also want to create a formula for the store data on the row immediately
above)

It seems that the offset function should come into play, but I'm
hitting a
wall on how to do this...any help?

Thanks

Vlookup and offset function?
 

Thanks to all for your help...The Index function got it done.

Gracias....

"vezerid" wrote:

If column B contains the value you want to retrieve and A contains the
store codes, how about:

=INDEX(B1:B100,MATCH(code,A1:A100,0)+1)

Does this help?
Kostis Vezerides

David B wrote:
I am trying to write a lookup function that will find a row of data for a
specific store (indicated by a store number, i.e. my "lookup value") then
return the data for the row immediately below the specified store's data. (I
also want to create a formula for the store data on the row immediately above)

It seems that the offset function should come into play, but I'm hitting a
wall on how to do this...any help?

Thanks