Offset Type Lookup

[Michael Lockwoo]

Hello all,

I'm working on an excel sheet that will extrapolate data from a
roster.

The idea is for when the name is entered, the corresponding shift and
location is automatically populated in to the field.

The crux of the problem lies in the lay out of the data is necessarily
stacked, Shift on one row, Location on the second row. Extracting the
row is a cinch, a simple Vlookup. THe problem comes when I try extract
the corrolating Location below it, I can't. I've tried the Offset
function, but I can't seem to get it to work for me, I won't accept
dynamic references.

There's probably a very simple Macro for it, but my skills aren't
advanced enough to dabble into the creation of my own macros..

Any assisance would be very much appreciated, I'm tearing my hair out
from this one.

Michael J. Lockwood

 

[Max]

.. Shift on one row, Location on the second row

Venturing a guess here based on the above,
how about trying HLOOKUP ?

 

[Robert_Steel]

Michael

I am slightly unsure of your data layout. But have assumed this

ColA ColB
NAME HEADING DATA HEADING
name 1 shift
location
name2 shift
location
...

The lookup will get your shift linked to a name but you also need
location.
I think an Index Match could do what you need
=INDEX(B2:B7,1+MATCH(A21,A2:A7,0))
lookup value in A21
The 1+ moves you down a row

hth RES

 

[Michael Lockwoo]

Thanks for the response!

I'll certainly give it a try. THe only thing is, being a duty roster,
alot of the data would be redundant.

Michael J. Lockwood