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]

It's been sent!... Thank a bundle, let me know when you receive it.

Sean

 

[seenfresh]

I need to clarify a few things with this Wb I may have been misunderstood according to my explanations!

This formula was created for me and should clarify some of the objectives:

=LOOKUP(2,1/((A$254:A$16910=E7)*(D$254:D$16910=C7)),M$254:M$16910)

This formula works, although their may be a more efficient formula or method that maybe someone can comprise...

I also have a question! Can someone explane this portion of the formula

LOOKUP(2,1/((A$254:A$16910=E7)

I'm not sure what the 2,1/ refers to?

Once again thank you for your help! Much appreciated

 

[just_jon]

{snip}
I also have a question! Can someone explane this portion of the formula

LOOKUP(2,1/((A$254:A$16910=E7)

I'm not sure what the 2,1/ refers to?

Once again thank you for your help! Much appreciated

See: http://www.mrexcel.com/board2/viewtopic.php?t=183723

 

[seenfresh]

Thanks Jon!

 

[Domenic]

After taking a look at the actual file, I see that the data is laid out differently than what I understood it to be. The layout, as is, makes it difficult to offer a more efficient solution. However, if you're willing to sort the data and use a helper column, a solution could be offered which would be more efficient. If interested in this approach, post back.

Hope this helps!

 

[seenfresh]

Yes absolutely

Any help that could create more efficient solutions would be amazing! The more efficient the better!

Thank you Domenic and Jon you guys are great! as is the Board!

I will do some more research and post anything that I think can be useful!

Thanks again!

Sean

 

[Domenic]

As I previously mentioned, sort your data first by Column A, then by Column D, and then by Column F, all in ascending order. Here's a short sample of how the data would be laid out, which includes two stations (WBAY and XYZ)...

A320:M327 contains...

Column A     Column D       Column F              Column M

WBAY			EdgeSuite		8/1/06							10
WBAY			EdgeSuite		8/31/06							20
WBAY			Net Storage		8/1/06							30
WBAY			Net Storage		8/31/06							40
XYZ			EdgeSuite		8/1/06							50
XYZ			EdgeSuite		8/31/06							60
XYZ			Net Storage		8/1/06							70
XYZ			Net Storage		8/31/06							80

Notice that there are no column labels or blank rows between each block of stations. Then you could simply use the following formula...

=VLOOKUP(B7,INDEX(D$320:D$327,MATCH(A7,A$320:A$327,0)):INDEX(M$320:M$327,MATCH(A7,A$320:A$327)),10)

...where B7 contains Net Storage, and A7 contains WBAY. If this is something that's going to be done monthly, then you may want to consider using VBA to sort the data and get it into the above layout.

 

[seenfresh]

That's a great idea!

I will give it a go and let you know how efficient it is!

As the formula used before without the sorting it works great!, but at the same time you are right if I am doing this monthly which I will be it would be best to carry out this function and create a VBA in which could sort and carry out the functional LOOKUPS, also if my data begins to expand the LOOKUP time will increase!

Thanks Domenic

After I perform this I will post my progress, it would be interesting to see how I can standardize this process.

Sean

 

[seenfresh]

Thanks

Domenic,

I have sorted the data from A320:A16910 by col A,D,F all in ascending order the data appears as follows see dispaly[2].

In cell F7 I have the following formula you have developed
=VLOOKUP(C7,INDEX(D$320:D$16910,MATCH(A7,A$320:A$16910,0)):INDEX(M$320:M$16910,MATCH(A7,A$320:A$16910)),10)

C7 represents Edge Suite Edition

After I run this I get the results from the first instance of (A7,A$320:A$16910)

I get the results from cell M320

I'm not sure why it isn't picking up the last instance of ABCG? This part of the formula should refer to the last instance right?
MATCH(A7,A$320:A$16910,0) the 0 should refer to the highest value

Would I need to insert a BIG NUM in this case?

Thanks again!

Sean

Akamai_Aug_Report3.csv
	A	B	C	D	E	F
7	ABCG	DataFinal	EdgeSuiteDeliveryEdition	NetStorage		0
8	ABCONO	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
9	ADHOC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
10	ADVOC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
11	AJC	DataFinal	EdgeSuiteDeliveryEdition	NetStorage
Akamai_Aug_Report3

Akamai_Aug_Report3.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
320	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/1/2006	0	0	0	0	0	0	0
321	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/2/2006	0	0	0	0	0	0	0
322	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/3/2006	0	0	0	0	0	0	0
323	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/4/2006	0	0	0	0	0	0	0
324	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/5/2006	0	0	0	0	0	0	0
325	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/6/2006	0	0	0	0	0	0	0
326	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/7/2006	0	0	0	0	0	0	0
327	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/8/2006	0	0	0	0	0	0	0
328	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/9/2006	0	0	0	0	0	0	0
329	ABCG	8/1/2006	8/31/2006	EdgeSuiteDeliveryEdition	13033	8/10/2006	0	0	0	0	0	0	0
Akamai_Aug_Report3

 

[Domenic]

The formula looks fine. Can you send me a sample file?