vlookup or index match???
 

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu the
other has an invoice.. on the invoice I need cell I 13 which is to be a route
# to reference E 11 (which has name chosen from a drop down list) and then
match the day of the week from the date cell H3.. and return the route
number from either an exact match from list on other sheet or allow me to
choose from a second drop down list eliminating the need to match the date in
above scenario.. hope this makes sense and someone can help.. am new to this
stuff

vlookup or index match???
 

Kathy,

I obviously don't know the specifics of the layout of your other sheet, but
a combination of the INDEX and MATCH functions can do the trick (as you
alluded to in your post title).

Let's say the sheet containing E11 and H3 is sheet1, and the other sheet is
sheet2, and your list in sheet 2 is in the array $A$1:$D$50, the something
like this should work:

=index(sheet2!$A$1:$D$50,match(sheet1!$E$11,index( sheet2!$A$1:$D$50,,1),0),match(sheet1!$H$3,index(s heet2!$A$1:$D$50,1,),0))

It is pretty messy, but I hope that helps.

DoubleZ

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu the
other has an invoice.. on the invoice I need cell I 13 which is to be a route
# to reference E 11 (which has name chosen from a drop down list) and then
match the day of the week from the date cell H3.. and return the route
number from either an exact match from list on other sheet or allow me to
choose from a second drop down list eliminating the need to match the date in
above scenario.. hope this makes sense and someone can help.. am new to this
stuff

vlookup or index match???
 

...thanks so much this does work.. I just need one more piece of advice.. for
the date I had to split off the day of the week from the actual date.. eg. H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4 monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu the
other has an invoice.. on the invoice I need cell I 13 which is to be a route
# to reference E 11 (which has name chosen from a drop down list) and then
match the day of the week from the date cell H3.. and return the route
number from either an exact match from list on other sheet or allow me to
choose from a second drop down list eliminating the need to match the date in
above scenario.. hope this makes sense and someone can help.. am new to this
stuff

vlookup or index match???
 

If H4 is a real date, rather than text, then H3 can be =H4, formatted as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if
you want H3 to be text.
--
David Biddulph

"kathy" wrote in message
...
..thanks so much this does work.. I just need one more piece of advice..
for
the date I had to split off the day of the week from the actual date.. eg.
H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4
monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu the
other has an invoice.. on the invoice I need cell I 13 which is to be a
route
# to reference E 11 (which has name chosen from a drop down list) and
then
match the day of the week from the date cell H3.. and return the route
number from either an exact match from list on other sheet or allow me to
choose from a second drop down list eliminating the need to match the
date in
above scenario.. hope this makes sense and someone can help.. am new to
this
stuff

vlookup or index match???
 

problem after this though it nullifies the index match from cell I13

"David Biddulph" wrote:

If H4 is a real date, rather than text, then H3 can be =H4, formatted as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if
you want H3 to be text.
--
David Biddulph

"kathy" wrote in message
...
..thanks so much this does work.. I just need one more piece of advice..
for
the date I had to split off the day of the week from the actual date.. eg.
H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4
monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu the
other has an invoice.. on the invoice I need cell I 13 which is to be a
route
# to reference E 11 (which has name chosen from a drop down list) and
then
match the day of the week from the date cell H3.. and return the route
number from either an exact match from list on other sheet or allow me to
choose from a second drop down list eliminating the need to match the
date in
above scenario.. hope this makes sense and someone can help.. am new to
this
stuff

vlookup or index match???
 

You know what you're trying to match, but we don't.
--
David Biddulph

"kathy" wrote in message
...
problem after this though it nullifies the index match from cell I13

"David Biddulph" wrote:

If H4 is a real date, rather than text, then H3 can be =H4, formatted as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if
you want H3 to be text.
--
David Biddulph

"kathy" wrote in message
...
..thanks so much this does work.. I just need one more piece of
advice..
for
the date I had to split off the day of the week from the actual date..
eg.
H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4
monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu
the
other has an invoice.. on the invoice I need cell I 13 which is to be
a
route
# to reference E 11 (which has name chosen from a drop down list)
and
then
match the day of the week from the date cell H3.. and return the
route
number from either an exact match from list on other sheet or allow me
to
choose from a second drop down list eliminating the need to match the
date in
above scenario.. hope this makes sense and someone can help.. am new
to
this
stuff

vlookup or index match???
 

ok we'll start over...as per previous query I needed cell I 13 on sheet 1 to
reference cell E11 on sheet one which drew ifs info from a drop down list on
sheet 2 A1:A57 and the day of the week in H3 on sheet 1 and return a route #
from sheet 2..H1:N57 so the formula below was provided and it is working upto
input dates of Thursday after which it will not provide the route number.. my
query was if I have to type in the day of the week for the reference is
there any way to have a date formulated cell provide that as each sheet has
to be dated with specifics say Oct 10 then friday to give me the correct
route #.. sorry it is really hard to put this on paper but hopefully you can
understand what I need..

"David Biddulph" wrote:

You know what you're trying to match, but we don't.
--
David Biddulph

"kathy" wrote in message
...
problem after this though it nullifies the index match from cell I13

"David Biddulph" wrote:

If H4 is a real date, rather than text, then H3 can be =H4, formatted as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd") if
you want H3 to be text.
--
David Biddulph

"kathy" wrote in message
...
..thanks so much this does work.. I just need one more piece of
advice..
for
the date I had to split off the day of the week from the actual date..
eg.
H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in H4
monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu
the
other has an invoice.. on the invoice I need cell I 13 which is to be
a
route
# to reference E 11 (which has name chosen from a drop down list)
and
then
match the day of the week from the date cell H3.. and return the
route
number from either an exact match from list on other sheet or allow me
to
choose from a second drop down list eliminating the need to match the
date in
above scenario.. hope this makes sense and someone can help.. am new
to
this
stuff

vlookup or index match???
 

No, I don't understand what you're trying to do, but I thought that the
question was how to get the day of the week, given a date in another cell.
That was what I answered. If someone else has a different understanding as
to what you are trying to ask, they will hopefully answer.
--
David Biddulph

"kathy" wrote in message
...
ok we'll start over...as per previous query I needed cell I 13 on sheet 1
to
reference cell E11 on sheet one which drew ifs info from a drop down list
on
sheet 2 A1:A57 and the day of the week in H3 on sheet 1 and return a route
#
from sheet 2..H1:N57 so the formula below was provided and it is working
upto
input dates of Thursday after which it will not provide the route number..
my
query was if I have to type in the day of the week for the reference is
there any way to have a date formulated cell provide that as each sheet
has
to be dated with specifics say Oct 10 then friday to give me the correct
route #.. sorry it is really hard to put this on paper but hopefully you
can
understand what I need..

"David Biddulph" wrote:

You know what you're trying to match, but we don't.
--
David Biddulph

"kathy" wrote in message
...
problem after this though it nullifies the index match from cell I13

"David Biddulph" wrote:

If H4 is a real date, rather than text, then H3 can be =H4, formatted
as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd")
if
you want H3 to be text.
--
David Biddulph

"kathy" wrote in message
...
..thanks so much this does work.. I just need one more piece of
advice..
for
the date I had to split off the day of the week from the actual
date..
eg.
H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in
H4
monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu
the
other has an invoice.. on the invoice I need cell I 13 which is to
be
a
route
# to reference E 11 (which has name chosen from a drop down list)
and
then
match the day of the week from the date cell H3.. and return the
route
number from either an exact match from list on other sheet or allow
me
to
choose from a second drop down list eliminating the need to match
the
date in
above scenario.. hope this makes sense and someone can help.. am
new
to
this
stuff

vlookup or index match???
 

Thanks David.. there was previous history to what I needed to accomplish
related to that cell and then this ..so will continue..

"David Biddulph" wrote:

No, I don't understand what you're trying to do, but I thought that the
question was how to get the day of the week, given a date in another cell.
That was what I answered. If someone else has a different understanding as
to what you are trying to ask, they will hopefully answer.
--
David Biddulph

"kathy" wrote in message
...
ok we'll start over...as per previous query I needed cell I 13 on sheet 1
to
reference cell E11 on sheet one which drew ifs info from a drop down list
on
sheet 2 A1:A57 and the day of the week in H3 on sheet 1 and return a route
#
from sheet 2..H1:N57 so the formula below was provided and it is working
upto
input dates of Thursday after which it will not provide the route number..
my
query was if I have to type in the day of the week for the reference is
there any way to have a date formulated cell provide that as each sheet
has
to be dated with specifics say Oct 10 then friday to give me the correct
route #.. sorry it is really hard to put this on paper but hopefully you
can
understand what I need..

"David Biddulph" wrote:

You know what you're trying to match, but we don't.
--
David Biddulph

"kathy" wrote in message
...
problem after this though it nullifies the index match from cell I13

"David Biddulph" wrote:

If H4 is a real date, rather than text, then H3 can be =H4, formatted
as
dddd (which would mean H3 also contains the date), or =TEXT(H4,"dddd")
if
you want H3 to be text.
--
David Biddulph

"kathy" wrote in message
...
..thanks so much this does work.. I just need one more piece of
advice..
for
the date I had to split off the day of the week from the actual
date..
eg.
H3
has monday h4 has Oct 6.. is there any way that when I put Oct 6 in
H4
monday
can populate in H3?

"kathy" wrote:

Hi.. need help to figure out how to:

two worksheets in same book.. one sheet has list for drop down menu
the
other has an invoice.. on the invoice I need cell I 13 which is to
be
a
route
# to reference E 11 (which has name chosen from a drop down list)
and
then
match the day of the week from the date cell H3.. and return the
route
number from either an exact match from list on other sheet or allow
me
to
choose from a second drop down list eliminating the need to match
the
date in
above scenario.. hope this makes sense and someone can help.. am
new
to
this
stuff