PDA

View Full Version : Vlookup Dynamic Criteria



Joey86
08-12-2013, 11:43 PM
Hi All, just needing some excel help with a vlookup that I'm needing. Ok so I have a massive data table on worksheet (Checklist (Table_owssvr_1)) and on the worksheet (Dashboard) I have the below table, 'Year Select' is a drop-down list selection of Years and 'Month Select' is the same. But what I'm needing to do is display the results of 1-31 days for those customers based on the Year and Month selection, the result I'm needing to display is the success rate on from the (Checklist) sheet. I've tried the below formula but no success, if anyone could help it'd be appreciated. Thanks


=IF(AND(Table_owssvr_1[[#All],[Year_Calc]],'Dashboard'!B2,Table_owssvr_1[[#All],[Month_Calc]],'Dashboard'!B3,Table_owssvr_1[[#All],[Day_Calc]],'Dashboard'!C4,Table_owssvr_1[[#All],[Customer]],'Dashboard'!B5),Table_owssvr_1[[#All],[Success Rate]],FALSE)





A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG


1



































2

Year Select

































3

Month Select

































4


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


5

Cust 1
Success Rate (Day 1)
































6

Cust 2

































7

Cust 3

































8

Cust 4

































9

Cust 5

Bob Phillips
08-12-2013, 11:48 PM
Can you post the workbook, or a subset of that workbook so that we can avoid re-inventing the (data) wheel?

Joey86
08-13-2013, 12:02 AM
Hi xld, thanks for your quick response, I've had to create a copy and remove all sensitive data out of it: 'www(dot)filedropper(dot)com/book1_6', sorry this forum won't let me post a link because I'm still a newbie and don't have enough points, also don't know if this will help with constructing a lookup for it but on the 'Checklist' worksheet, because the data's dynamic and new data will be added all the time, I have a vba macro to column fill a 'RowNum' column with a number going down for how many rows have been filled - 1(title row).

mancubus
08-13-2013, 04:02 AM
hi. you need at least 5 posts to be able to paste a link here. but you can upload a file. click Go Advanced button just below the Quick Reply panel. scroll down to Additional Options then click Manage Attachments. browse for your file and upload here.

Joey86
08-13-2013, 04:34 AM
Thanks mancubus, that's awesome didn't know I could attach files like that :thumb

Ringhal
08-13-2013, 05:52 AM
Have you thought about using a PivotTable?

Joey86
08-13-2013, 05:48 PM
Hi Ringhal, I have considered it, although at the end of the 31 days I'll need a 'Monthly Total' which would be an average of scores and if no scores are completed for a particular day then I need to have that cell marked as 'TBA', plus I'll need to put in some conditional formatting for the scores.

Joey86
08-15-2013, 03:39 AM
Any luck with this? Still having trouble figuring it out