Trouble with VLookup & Match Formula

[seenfresh]

I am having trouble with a VLOOKUP with multiple dependants, maybe someone could help me or notice what I am doing wrong.

I am trying to VLOOKUP:

The first Display[1] shows a partial list of stations in column A and trimed in column F for VL purposes

In the second Dispay[2] I would like to VLookup from Display[1] to Display[2] based on these dependencies:

1) Search all of column A and match station name in A134 "WBAY" with "WBAY" in A 352 (must be the last occurance because of next dependencies)
2) Match C134 with C352
2) Then match find the last date in column F thus 8/31/2006
3) Then if all these hold true in the same row as the A134 -> A352 station name match return value from column M

I have this function but it returns #N/A could anyone spot a problem with this?

=VLOOKUP(C134,INDEX(D$320:D$1000,MATCH(A134,A$320:A$1000,0)):INDEX(M$320:M$1000,MATCH(A134,A$320:A$1000)),10)


Sorry about the Essay!

If anyone could help I would remember you when I buy my next Lottery Ticket!

This board is awesome!

Thanks,

Sean

Akamai_Aug_Report3.csv
	A	B	C	D	E	F	G
134	WBAY	DataFinal	EdgeSuiteDeliveryEdition	NetStorage		ABCG	#N/A
135	WBDC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
136	WBKO	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
137	WBNS	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
138	WBOC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
139	WBTV	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
Akamai_Aug_Report3

Akamai_Aug_Report3.csv
	A	B	C	D	E	F	G
134	WBAY	DataFinal	EdgeSuiteDeliveryEdition	NetStorage		ABCG	#N/A
135	WBDC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
136	WBKO	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
137	WBNS	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
138	WBOC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
139	WBTV	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
Akamai_Aug_Report3

 

[seenfresh]

Sorry Here is the proper Display[2]

Akamai_Aug_Report3.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M
347	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/26/2006	1.58	0	96.6	1.58	0	240182.5	240182.5
348	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/27/2006	1.54	0	96.6	1.54	0	241311.9	241311.9
349	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/28/2006	1.48	0	96.6	1.48	0	242983.5	242983.5
350	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/29/2006	1.44	0	96.6	1.44	0	245463.5	245463.5
351	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/30/2006	1.4	0	96.6	1.4	0	247441.6	247441.6
352	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/31/2006	1.38	0	96.6	1.38	0	248935.9	248935.9
Akamai_Aug_Report3

 

[Domenic]

If I understood you correctly, Column D in the source table can contain either 'EdgeSuite Delivery Edition' or 'Net Storage'. Is this correct? Also, will the lookup value always be found in Column D?

 

[seenfresh]

Yes

Yes Sir, Their is a section of data ex. Display[2] for each station the is this Display[1] also there is another section that is similar to Display[2] that yes is dependant on Net Storage

These have to be dependants because the "WBAY" station is included in both Displays and I must filter(make sure I am VLooking according to Net Storage or Edge Suite Delivery Edition)

Thanks you very much!

Hope this helps

Sean

 

[Domenic]

I'm still a little unclear, but it seems to me that you only need to change the lookup value for the first formula posted...

=VLOOKUP(D134,INDEX(D$320:D$1000,MATCH(A134,A$320:A$1000,0)):INDEX(M$320:M$1000,MATCH(A134,A$320:A$1000)),10)

Does this return the desired result? If not, can you post a sample of the data that includes both 'EdgeSuite Delivery Edition' and 'Net Storage'?

 

[Domenic]

Also, I'm assuming that the data is sorted first by Column A, then by Column D, and then by Column F, all in ascending order. Is this correct?

 

[seenfresh]

Actually is there anyway I can email you the Ws might save a lot of explanation time!

And I can include some comments and would be more efficient for you?

thnx

Sean

 

[seenfresh]

=VLOOKUP(D134,INDEX(D$320:D$1000,MATCH(A134,A$320:A$1000,0)):INDEX(M$320:M$1000,MATCH(A134,A$320:A$1000)),10)

Doesn't return the proper result it returns Net Storage the cell D134, I am trying to get it to return cell M352

If you'd like I can email the wb

Thx for your help and patience!

 

[Domenic]

Can you post a representative sample of your data, which includes EdgeSuite and Net Storage?

 

[seenfresh]

Here it is!

Here is a Display that is the same as Display[2] but shows the column labels and start date of the info starting at 8/1/2006.

Akamai_Aug_Report3.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
321	Contract/ReportingGroup	UsageStartDate	UsageEndDate	Product	CPCodes	TimeStamp	Combined95/5Mbps	Http95/5Mbps	MaxMbps	Streaming95/5Mbps	TotalHTTPMB	CombinedTotalMB	TotalStreamingMB
322	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/1/2006	0.94	0	1.54	0.94	0	3806.18	3806.18
323	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/2/2006	0.99	0	1.84	0.99	0	7767.78	7767.78
324	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/3/2006	0.87	0	1.84	0.87	0	9002.74	9002.74
325	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/4/2006	0.82	0	1.84	0.82	0	10758.83	10758.83
326	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/5/2006	0.76	0	1.84	0.76	0	11754.86	11754.86
327	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/6/2006	0.69	0	1.84	0.69	0	12548.18	12548.18
328	WBAY	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	8823	8/7/2006	0.69	0	1.84	0.69	0	14225.04	14225.04
Akamai_Aug_Report3

Here is a sample of the Net Storage section of data for WBAY with the column headings and start date

Akamai_Aug_Report3.csv
	A	B	C	D	E	F	G
355	Contract/ReportingGroup	UsageStartDate	UsageEndDate	Product	CPCodes	TimeStamp	95/5GB
356	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/1/2006	3.5
357	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/2/2006	3.24
358	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/3/2006	3.22
359	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/4/2006	3.18
360	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/5/2006	3.17
361	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/6/2006	3.24
362	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/7/2006	3.6
363	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/8/2006	3.6
364	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/9/2006	3.6
365	WBAY	8/1/2006	8/31/2006	NetStorage	8823	8/10/2006	3.6
Akamai_Aug_Report3

As you can see the data is sorted by column A then B then F

My whole booklet has station names such as Display[1] then includes Display[2] (EdgeSuite) and Display[3] (Netstorage) one after another for each station presented in Display[1]

I am wondering if I am able to VLOOKUP the station then match Column C in Display[1] column D EdgeSuite Display[2] or/and column D Display[1] with Column D NetStorage in Display [3] if true continue to match the end date of Time Stamp column F and return column M for EdgeSuite and Column G for Net Storage

I would probably have to do two seperate VLOOKUPS 1 for EdgeSuite Display[2]and 1 for NetStorage Display[3]

Hope this helps! Thanks again for you help!