vlookup or index match???

[kathy]

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

 

[DoubleZ]

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(sheet2!$A$1:$D$50,1,),0))

It is pretty messy, but I hope that helps.

DoubleZ

 

[kathy]

...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]

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

 

[kathy]

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..

 

[kathy]

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