Offset Type Lookup
 

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


--
Michael Lockwoo


------------------------------------------------------------------------
Michael Lockwoo's Profile: http://www.excelforum.com/member.php...o&userid=31811
View this thread: http://www.excelforum.com/showthread...hreadid=525979

Offset Type Lookup
 

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

Venturing a guess here based on the above,
how about trying HLOOKUP ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michael Lockwoo"
<Michael.Lockwoo.255omm_1143173403.2218@excelfor um-nospam.com wrote in
message news:Michael.Lockwoo.255omm_1143173403.2218@excelf orum-nospam.com...

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


--
Michael Lockwoo


------------------------------------------------------------------------
Michael Lockwoo's Profile:
http://www.excelforum.com/member.php...o&userid=31811
View this thread: http://www.excelforum.com/showthread...hreadid=525979

Offset Type Lookup
 

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

Offset Type Lookup
 

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


--
Michael Lockwoo


------------------------------------------------------------------------
Michael Lockwoo's Profile: http://www.excelforum.com/member.php...o&userid=31811
View this thread: http://www.excelforum.com/showthread...hreadid=525979