Trouble with VLookup & Match Formula

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
ABCDEFG
134WBAYDataFinalEdgeSuiteDeliveryEditionNetStorageABCG#N/A
135WBDCDataFinalEdgeSuiteDeliveryEditionNetStorage
136WBKODataFinalEdgeSuiteDeliveryEditionNetStorage
137WBNSDataFinalEdgeSuiteDeliveryEditionNetStorage
138WBOCDataFinalEdgeSuiteDeliveryEditionNetStorage
139WBTVDataFinalEdgeSuiteDeliveryEditionNetStorage
Akamai_Aug_Report3
Akamai_Aug_Report3.csv
ABCDEFG
134WBAYDataFinalEdgeSuiteDeliveryEditionNetStorageABCG#N/A
135WBDCDataFinalEdgeSuiteDeliveryEditionNetStorage
136WBKODataFinalEdgeSuiteDeliveryEditionNetStorage
137WBNSDataFinalEdgeSuiteDeliveryEditionNetStorage
138WBOCDataFinalEdgeSuiteDeliveryEditionNetStorage
139WBTVDataFinalEdgeSuiteDeliveryEditionNetStorage
Akamai_Aug_Report3
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Sorry Here is the proper Display[2]
Akamai_Aug_Report3.csv
ABCDEFGHIJKLM
347WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/26/20061.58096.61.580240182.5240182.5
348WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/27/20061.54096.61.540241311.9241311.9
349WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/28/20061.48096.61.480242983.5242983.5
350WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/29/20061.44096.61.440245463.5245463.5
351WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/30/20061.4096.61.40247441.6247441.6
352WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/31/20061.38096.61.380248935.9248935.9
Akamai_Aug_Report3
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
=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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Can you post a representative sample of your data, which includes EdgeSuite and Net Storage?
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
ABCDEFGHIJKLMN
321Contract/ReportingGroupUsageStartDateUsageEndDateProductCPCodesTimeStampCombined95/5MbpsHttp95/5MbpsMaxMbpsStreaming95/5MbpsTotalHTTPMBCombinedTotalMBTotalStreamingMB
322WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/1/20060.9401.540.9403806.183806.18
323WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/2/20060.9901.840.9907767.787767.78
324WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/3/20060.8701.840.8709002.749002.74
325WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/4/20060.8201.840.82010758.8310758.83
326WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/5/20060.7601.840.76011754.8611754.86
327WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/6/20060.6901.840.69012548.1812548.18
328WBAY8/1/20068/31/2006EdgeSuiteDeliveryEdition88238/7/20060.6901.840.69014225.0414225.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
ABCDEFG
355Contract/ReportingGroupUsageStartDateUsageEndDateProductCPCodesTimeStamp95/5GB
356WBAY8/1/20068/31/2006NetStorage88238/1/20063.5
357WBAY8/1/20068/31/2006NetStorage88238/2/20063.24
358WBAY8/1/20068/31/2006NetStorage88238/3/20063.22
359WBAY8/1/20068/31/2006NetStorage88238/4/20063.18
360WBAY8/1/20068/31/2006NetStorage88238/5/20063.17
361WBAY8/1/20068/31/2006NetStorage88238/6/20063.24
362WBAY8/1/20068/31/2006NetStorage88238/7/20063.6
363WBAY8/1/20068/31/2006NetStorage88238/8/20063.6
364WBAY8/1/20068/31/2006NetStorage88238/9/20063.6
365WBAY8/1/20068/31/2006NetStorage88238/10/20063.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!
 

Forum statistics

Threads
1,136,612
Messages
5,676,800
Members
419,651
Latest member
alexanderguhr

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top