Formula Check? Returning N/A

[amie797]

Hoping for help. I have spent along time searching the forums and although I have gotten closer, still not getting the result I need. I have 2 sheets. Data and sheet one. Data has a ton of info and I am looking for a formula to put into Sheet1 that take a store number (Sheet1 B2), find a store number on Data tab (Data B2:B2543), find a specific time for that store in another column (Data E2:E2543) and then return the number shown in Column O when those two other columns show the required numbers. I have this formula but I know it's not correct to give you a better idea.

=INDEX(Data!$F$8:$M$2538, MATCH(Sheet1!B2, Data!$M$8:$M$2538,0), MATCH(C1, Data!$E$8:$M$2538,0))

Basically have lets say 5 columns on Data -
Store Number Time City State Star

I have all the store numbers and times listed on Sheet1
Store 7am 8am 9am
xxxx ????
xxxx
xxxx

What I can put in Sheet1 where the ???? are that will look at DATA, compare store number and time and give me the Star number that matches??
Thank you so much for your help. Vlookup and pivots just aren't cutting it for this one and I am over my head.

[SDCh]

Please upload example file

[amie797]

I am sorry. I don't see how to do that. Here is a copy / paste.

Data Tab
Dist Store Day Time STAR
95 486 Mon, Mar 04 7:00 AM 0
95 486 Mon, Mar 04 8:00 AM 2.17
95 486 Mon, Mar 04 9:00 AM 3.29
95 486 Mon, Mar 04 10:00 AM 4.86
95 486 Mon, Mar 04 11:00 AM 6.71
95 486 Mon, Mar 04 12:00 PM 9.67
95 486 Mon, Mar 04 1:00 PM 6
95 486 Mon, Mar 04 2:00 PM 9
95 486 Mon, Mar 04 3:00 PM 10.67
95 486 Mon, Mar 04 4:00 PM 11.6
95 486 Mon, Mar 04 5:00 PM 11
95 486 Mon, Mar 04 6:00 PM 8.8
95 486 Mon, Mar 04 7:00 PM 4.8
95 486 Mon, Mar 04 8:00 PM 5.2
95 486 Mon, Mar 04 9:00 PM 0
95 486 Mon, Mar 04 10:00 PM 0
86 488 Mon, Mar 04 7:00 AM 0
86 488 Mon, Mar 04 8:00 AM 2.33
86 488 Mon, Mar 04 9:00 AM 4.2
86 488 Mon, Mar 04 10:00 AM 7.5
86 488 Mon, Mar 04 11:00 AM 5.29
86 488 Mon, Mar 04 12:00 PM 7.71
...

Sheet1
Dist Store 8:00 AM 9:00 AM 10:00 AM 11AM 12:00 PM 1:00 PM 2:00 PM 3:00 PM 4:00 PM 5:00 PM
28 494 #N/A
28 814
28 818
28 846
28 874
28 908
28 934

[SDCh]

For attach file:
Click Go Advanced button >> scroll down >> click manage attachments buttone (new window will show up) >> add files >> select file>> choose the file >> open >> upload files >> done >> submit reply

By the way it's only check Dist, Store and Time? You got that plenty with the day.

[amie797]

Location by hour.xls

Did this work?
So I am trying to find a the Star # for a store at 7am, then for 8am and so on in Sheet1

[SDCh]

You can do that with pivot table.

[Pauleyb]

Whoops. Just saw you have Excel 2003. You'll have to use a sumproduct if you want to use a formula (or use the pivot table provided by SDCh). Again in C2
=SUMPRODUCT(Data!$F$2:$F$2532,--(Sheet1!$B2=Data!$B$2:$B$2532),--(Sheet1!C$1=--Data!$E$2:$E$2532))

[Pauleyb]

In Sheet1!C2:
=SUMIFS(Data!$F$2:$F$2532,Data!$B$2:$B$2532,Sheet1!$B2,Data!$E$2:$E$2532,Sheet1!C$1)
Then drag the formula across and down.

Note: you do have the time on the Data sheet as text and on the Sheet1 sheet as numeric. The equation above auto-converts, but that is one reason your match would not work.

[amie797]

Pauleyb - I get #NAME?

Thank you SDch - I am going to look at pivot tables again and see if it gives me the flexibility I want. I was doing mine vertically and was too hard to read all the info.

[amie797]

With the pivot table - my main issue is that the hours or times get out of order. Is there a way to fix that?

[SDCh]

Add new column with this formula

=TIMEVALUE(D2)

Note : D2 are column Time, with this you have time with time format.
If you make it vertical: Row Label: Time (use time format), Column Labels: Dist, Store.

[amie797]

Thank you! I have the pivot table working and it looks like it will work in that format.

[SDCh]

Glad hear your problem solved, and thanks for add rep.