Hi guys, I got a question and Fav to ask

I have a sheet that has a series of Dates being the Mondays dates through
the year next to each date is a listing of 4 names.

EG:
Date A B C D
11-Jun Bob Frank Gareth Trevor
18-Jun Trevor Bob Frank Gareth

What i want to do is this.

I have a sheet that has the Current date =today() {dd-mmm} i want it to take
that value and find it so should the date be 13-JUN how do i get it to return
the value from say A3 because that date falls into the 11-Jun section?

So basically to clarify i want to do a Vlookup on a name that falls on the
Mondays date when compared to todays date and then return the A,B,C or D at
the top. Can this be done? and how would i do it?

Thanks in advance for any assist.

I think I'd use the MATCH and INDEX functions to do this. Take a look at
those and if you have other questions, come back.

"Gareth - Network analyst." wrote:

Hi guys, I got a question and Fav to ask

I have a sheet that has a series of Dates being the Mondays dates through
the year next to each date is a listing of 4 names.

EG:
Date A B C D
11-Jun Bob Frank Gareth Trevor
18-Jun Trevor Bob Frank Gareth

What i want to do is this.

I have a sheet that has the Current date =today() {dd-mmm} i want it to take
that value and find it so should the date be 13-JUN how do i get it to return
the value from say A3 because that date falls into the 11-Jun section?

So basically to clarify i want to do a Vlookup on a name that falls on the
Mondays date when compared to todays date and then return the A,B,C or D at
the top. Can this be done? and how would i do it?

Thanks in advance for any assist.

Hi Barb

maybe I'm not thinking straight but my match dont wanna wok on its own never
mind getting the whole formula.

OH here what I am doing exactly, I have a sheet with shifts layed out in the
format i said mondays Date with the guys working to the right of it and above
all of this is a Letter for the shift (A,B,C,D,etc) i have created a sheet
now that has the current date on i want it to take a Name in cell D3 and look
it up returning the shift they are on.

so it needs to first find the Date, take this week 11-Jun is the Monday date
and today is the 13-Jun, so it must look in the 11-Jun line for the D3 (the
name) Value then goto the top of the page and return the Shift Say I in this
instance.

im prob looking to high for this and i have all but fried my brain now
trying to do it.


"Barb Reinhardt" wrote:

I think I'd use the MATCH and INDEX functions to do this. Take a look at
those and if you have other questions, come back.

"Gareth - Network analyst." wrote:

Hi guys, I got a question and Fav to ask

I have a sheet that has a series of Dates being the Mondays dates through
the year next to each date is a listing of 4 names.

EG:
Date A B C D
11-Jun Bob Frank Gareth Trevor
18-Jun Trevor Bob Frank Gareth

What i want to do is this.

I have a sheet that has the Current date =today() {dd-mmm} i want it to take
that value and find it so should the date be 13-JUN how do i get it to return
the value from say A3 because that date falls into the 11-Jun section?

So basically to clarify i want to do a Vlookup on a name that falls on the
Mondays date when compared to todays date and then return the A,B,C or D at
the top. Can this be done? and how would i do it?

Thanks in advance for any assist.

=INDEX(B1:G1,MIN(IF((A2:A20=TODAY()-WEEKDAY(TODAY(),2)+1)*(B2:G20="Bill"),ROW(A2:A20)-MIN(ROW(A2:A20))+1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
Hi Barb

maybe I'm not thinking straight but my match dont wanna wok on its own
never
mind getting the whole formula.

OH here what I am doing exactly, I have a sheet with shifts layed out in
the
format i said mondays Date with the guys working to the right of it and
above
all of this is a Letter for the shift (A,B,C,D,etc) i have created a sheet
now that has the current date on i want it to take a Name in cell D3 and
look
it up returning the shift they are on.

so it needs to first find the Date, take this week 11-Jun is the Monday
date
and today is the 13-Jun, so it must look in the 11-Jun line for the D3
(the
name) Value then goto the top of the page and return the Shift Say I in
this
instance.

im prob looking to high for this and i have all but fried my brain now
trying to do it.


"Barb Reinhardt" wrote:

I think I'd use the MATCH and INDEX functions to do this. Take a look
at
those and if you have other questions, come back.

"Gareth - Network analyst." wrote:

Hi guys, I got a question and Fav to ask

I have a sheet that has a series of Dates being the Mondays dates
through
the year next to each date is a listing of 4 names.

EG:
Date A B C D
11-Jun Bob Frank Gareth Trevor
18-Jun Trevor Bob Frank Gareth

What i want to do is this.

I have a sheet that has the Current date =today() {dd-mmm} i want it to
take
that value and find it so should the date be 13-JUN how do i get it to
return
the value from say A3 because that date falls into the 11-Jun section?

So basically to clarify i want to do a Vlookup on a name that falls on
the
Mondays date when compared to todays date and then return the A,B,C or
D at
the top. Can this be done? and how would i do it?

Thanks in advance for any assist.

Let's do this in steps

=MATCH(A1,B1:B10,1)

A1 is the value that you want to match
B1:B10 is the range that you are searching in
1 indicates the match type.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

OK, got that so far?

Now let's look at INDEX
=INDEX(A1:D10,F4,1)

A1:D10 is the range that you are searching in. Think of this in the same
sense as the range for the VLOOKUP.

F4 would be replaced with your MATCH function to get the ROW Number. You
may need to add some #'s to this if your match function didn't start at row 1.

1 would be replaced with your column number.

Clear as mud?

Barb Reinhardt

"Gareth - Network analyst." wrote:

Hi Barb

maybe I'm not thinking straight but my match dont wanna wok on its own never
mind getting the whole formula.

OH here what I am doing exactly, I have a sheet with shifts layed out in the
format i said mondays Date with the guys working to the right of it and above
all of this is a Letter for the shift (A,B,C,D,etc) i have created a sheet
now that has the current date on i want it to take a Name in cell D3 and look
it up returning the shift they are on.

so it needs to first find the Date, take this week 11-Jun is the Monday date
and today is the 13-Jun, so it must look in the 11-Jun line for the D3 (the
name) Value then goto the top of the page and return the Shift Say I in this
instance.

im prob looking to high for this and i have all but fried my brain now
trying to do it.


"Barb Reinhardt" wrote:

I think I'd use the MATCH and INDEX functions to do this. Take a look at
those and if you have other questions, come back.

"Gareth - Network analyst." wrote:

Hi guys, I got a question and Fav to ask

I have a sheet that has a series of Dates being the Mondays dates through
the year next to each date is a listing of 4 names.

EG:
Date A B C D
11-Jun Bob Frank Gareth Trevor
18-Jun Trevor Bob Frank Gareth

What i want to do is this.

I have a sheet that has the Current date =today() {dd-mmm} i want it to take
that value and find it so should the date be 13-JUN how do i get it to return
the value from say A3 because that date falls into the 11-Jun section?

So basically to clarify i want to do a Vlookup on a name that falls on the
Mondays date when compared to todays date and then return the A,B,C or D at
the top. Can this be done? and how would i do it?

Thanks in advance for any assist.

OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4 getting a
column number then using that column number in B2:J2 and return the Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the value
in C4

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4 getting a
column number then using that column number in B2:J2 and return the Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the value
in C4

OK if i take it all and copythe hole thing as is with my values and
CRTL-SHIFT-Enter then it keeps returning the 8th value "H"

dunno what im doing wrong here, if i user the Match and index i get
everything i want if i tell it what columb to return the value from

"Bob Phillips" wrote:

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4 getting a
column number then using that column number in B2:J2 and return the Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the value
in C4

Hopefully this is better

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
OK if i take it all and copythe hole thing as is with my values and
CRTL-SHIFT-Enter then it keeps returning the 8th value "H"

dunno what im doing wrong here, if i user the Match and index i get
everything i want if i tell it what columb to return the value from

"Bob Phillips" wrote:

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4 getting a
column number then using that column number in B2:J2 and return the
Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the
value
in C4

Ok this still aint working, Can you mail me cause i see your Mail address not
listed, then i can mail the doc to you to check maybe it will work better if
you can actually see whats happening, maybe im doing something wrong still or
you getting wrong locations for formula.?
Then we can just post the working formula here lol

"Bob Phillips" wrote:

Hopefully this is better

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
OK if i take it all and copythe hole thing as is with my values and
CRTL-SHIFT-Enter then it keeps returning the 8th value "H"

dunno what im doing wrong here, if i user the Match and index i get
everything i want if i tell it what columb to return the value from

"Bob Phillips" wrote:

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4 getting a
column number then using that column number in B2:J2 and return the
Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the
value
in C4

I can't mail you because you are on the web group, but you should be able to
work my email out from my signature.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
Ok this still aint working, Can you mail me cause i see your Mail address
not
listed, then i can mail the doc to you to check maybe it will work better
if
you can actually see whats happening, maybe im doing something wrong still
or
you getting wrong locations for formula.?
Then we can just post the working formula here lol

"Bob Phillips" wrote:

Hopefully this is better

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK if i take it all and copythe hole thing as is with my values and
CRTL-SHIFT-Enter then it keeps returning the 8th value "H"

dunno what im doing wrong here, if i user the Match and index i get
everything i want if i tell it what columb to return the value from

"Bob Phillips" wrote:

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4
getting a
column number then using that column number in B2:J2 and return the
Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd
Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the
value
in C4

BWAHAHAHAHAHA I got it hahahahahahaha :D

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,1),0),0))

Lol you forgot a 1 :D

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

Ah the days of machine code finally payed off BWAHAHAHAHAHAHAHAHAHAHA

Thanks alot Bob for all of the assist and for all the assist you were
willing to give me here. I'm sure your just as annoyed with this bit of code
as i am now lol

Thanks again :D

"Bob Phillips" wrote:

I can't mail you because you are on the web group, but you should be able to
work my email out from my signature.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
Ok this still aint working, Can you mail me cause i see your Mail address
not
listed, then i can mail the doc to you to check maybe it will work better
if
you can actually see whats happening, maybe im doing something wrong still
or
you getting wrong locations for formula.?
Then we can just post the working formula here lol

"Bob Phillips" wrote:

Hopefully this is better

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK if i take it all and copythe hole thing as is with my values and
CRTL-SHIFT-Enter then it keeps returning the 8th value "H"

dunno what im doing wrong here, if i user the Match and index i get
everything i want if i tell it what columb to return the value from

"Bob Phillips" wrote:

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4
getting a
column number then using that column number in B2:J2 and return the
Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd
Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change the
value
in C4

No I didn't, I used ,0 because I was expecting an exact match. That is why I
adjust today's date to get that exact match.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
BWAHAHAHAHAHA I got it hahahahahahaha :D

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,1),0),0))

Lol you forgot a 1 :D

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

Ah the days of machine code finally payed off BWAHAHAHAHAHAHAHAHAHAHA

Thanks alot Bob for all of the assist and for all the assist you were
willing to give me here. I'm sure your just as annoyed with this bit of
code
as i am now lol

Thanks again :D

"Bob Phillips" wrote:

I can't mail you because you are on the web group, but you should be able
to
work my email out from my signature.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
Ok this still aint working, Can you mail me cause i see your Mail
address
not
listed, then i can mail the doc to you to check maybe it will work
better
if
you can actually see whats happening, maybe im doing something wrong
still
or
you getting wrong locations for formula.?
Then we can just post the working formula here lol

"Bob Phillips" wrote:

Hopefully this is better

=INDEX('2nd Line Schedule'!B2:J2,MATCH(C4,INDEX('2nd Line
Schedule'!B3:J39,MATCH(TODAY()-WEEKDAY(TODAY(),2)+1,'2nd Line
Schedule'!A3:A39,0),0),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Gareth - Network analyst."

wrote in message
...
OK if i take it all and copythe hole thing as is with my values and
CRTL-SHIFT-Enter then it keeps returning the 8th value "H"

dunno what im doing wrong here, if i user the Match and index i get
everything i want if i tell it what columb to return the value from

"Bob Phillips" wrote:

did you array enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Gareth - Network analyst."

wrote in message
...
OK Great I follow that BUT...

thats fine for returning the value In a known column yes :D

Now I want it to find in that row the name I enter in Cell C4
getting a
column number then using that column number in B2:J2 and return
the
Cell
value there.

I tried adapting your other code but it gives the wrong value.

=INDEX('2nd Line Schedule'!B2:J2,MIN(IF(('2nd Line
Schedule'!A3:A39=TODAY()-WEEKDAY(TODAY(),2)+1)*('2nd Line
Schedule'!B2:J39=C4),ROW('2nd Line Schedule'!A3:A39)-MIN(ROW('2nd
Line
Schedule'!A3:A39))+1)))

it continually returns the second columbs value even if i change
the
value
in C4