Dynamic Lookup

glaupie

New Member
Hi,

I have a sheet which shows responses from stores (just a "YES" if they responded). The fields are:
Store Number, Store Name, Date, Response.

Stores are supposed to respond every day but they do not all do so.
So I will for instance have 100 stores, on the 25th 20 will respond, on the 26th 15 will, and so forth.

So there are tons of duplicates:

 5554 Store A 2012/06/25 YES 2234 Store B 2012/06/25 YES 1236 Store C 2012/06/25 YES 3939 Store D 2012/06/26 YES 1236 Store C 2012/06/26 YES 7788 Store E 2012/06/26 YES 5545 Store A 2012/06/27 YES 1236 Store C 2012/06/27 YES 7788 Store E 2012/06/27 YES 1896 Store F 2012/06/27 YES

<TBODY>
</TBODY>

So you can see store C responded on all 3 days, but stoe A only on the 25th and 27th.

Now on another master store sheet I want to see, per store, who responded one ach day:

 Store # Name 2012/06/25 2012/06/26 2012/06/27 2012/06/28 5554 Store A YES YES 1236 Store C YES YES YES

<TBODY>
</TBODY>

Daily I will get new stores added to teh responded sheet (this is sorted by date).

On my master sheet I have a formula which looks to see if the store number is in the table and if the response is yes and the date is the same as in the formula. So in the column above for the 26th I have:

=IF(AND(VLOOKUP(B266,'Master Store Feedback'!B\$50:E\$68,4,FALSE)="Yes",VLOOKUP(B266,'Master Store Feedback'!B\$50:E\$68,3,FALSE)=DATEVALUE("2012/06/25"))=TRUE,"YES","#N/A")

And for the 27th I just change the date.

Problem is I always only pick up the first value in the lookup. So if a store responded on more than one day, then it will match on the first day, but then in the next column it will again find that store number at the top and thed ate won't match (even though it will if it keeps looking down the line).

No idea what to do - this is pre-exisiting files that I am trying to help someone with and not at all teh way I would have done it.

Basically I want that formula to only "do the lookup" on the range where the date is equal to the one in the formula. Without having to change the formula daily.

Any ideas?

G

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You could just use a pivot table if that's an option? Then if you insert new data you can just refresh the pivot table.

Yes I have a pivot in there - but the way they want to see it is on the master file with all the stores (including the ones with no responses).

Think I will redo the whole way it is set up - thank you anyway for the quick response.

G

Would the index and match functions work here?

I saw posts of them when I was googling around earlier - will read up a bit because I've nevr used them before

Replies
5
Views
185
Replies
10
Views
344
Replies
4
Views
205
Replies
5
Views
304
Replies
2
Views
153

1,207,436
Messages
6,078,542
Members
446,345
Latest member
MicCh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back