more re index and match

[kathy]

Was sent this formula by Double Z.. one issue with it... on my sheet 2 my
array is actually A1:N57
to match to the drop down list in E 11 on sheet 2( I am using info From
A1:A57 store names sheet 2) then indexing same to match the date H3 sheet1
so that the info for Route # needed in I 13( Sunday to Saturday stored in
H1:N57 on sheet two)will populate... problem is formula will not correctly
match the selections after I get to entering Thursday in H:3 please help some
more?

=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))

 

[Bernie Deitrick]

Kathy,

If Thursday is in cell E1, it won't be found since your match is only
looking through column D. Try this instead:

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

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Try this:

=INDEX(Sheet2!$A$1:$N$57,MATCH(Sheet1!$E$11,Sheet2!$A$1:$A$57,0),MATCH(Sheet1!$H$3,Sheet2!$A$1:$N$1,0))

 

[kathy]

yes this one did finally work.. one more query for you.. H3 is the day of the
week.. eg sunday thru Saturday I'd like to enter the actual date in the cell
next I 3 then have the day of the week pop up in H3 but that spoils the
match formula.. any ideas or advice on how I could accomplish this?

 

[T. Valko]

Try this:

I3 = some date like 10/27/2008

Enter this formula in H3:

=TEXT(I3,"dddd")

That will return the long weekday name like Monday. If you want the short
weekday name like Mon just remove one of the d's.

 

[kathy]

SWEET!!!! your help is so much appreciated... This will eliminate probably
half hour of data entry every monday.. Thank you!

 

[T. Valko]

You're welcome. Thanks for the feedback!