Three Dimensional Lookup Problem

tomdabom76

New Member
Joined
Oct 10, 2006
Messages
10
Hey everyone,

This is my first post in this forum. I am using Windows XP and Excel 2002. The following are the tables that I am working with. I am trying to set up an array lookup that will work even as dates are added. I have tried an index function with match functions to try and get this to work, but no success. What I would like to do for example is search the whole range for Zelda on 10/1/06 and get the appropriate number of downloads. I hope this is clear. Thanks in advance for your help
Book1
ABCD
1Zelda
2DateDownloads
310/1/20065
410/2/200610
5
6SuperMario
7DateDownloads
810/1/20065
Sheet1
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try...

=VLOOKUP(E1,INDEX(A1:A8,MATCH(D1,A1:A8,0)):B8,2,0)

...where D1 contains Zelda, and E1 ccontains 10/1/06.

Hope this helps!
 
Upvote 0
OK. I tried the formula and it worked....except in a few instances. I run into a problem if for example Zelda does not have the 10/2/06 cell, but Super Mario does. When I do the lookup searching for Zelda and the date, the results will be the download for Mario on 10/2/06. I tried to adjust the formula for an exact match but it did not work. Any suggestions?
 
Upvote 0
Sorry, my mistake! If each set of data is separated by a blank row, try the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try...

=VLOOKUP(E1,INDEX(A1:A8,MATCH(D1,A1:A8,0)+2):INDEX(B1:B8,LOOKUP(BigNum,CHOOSE({1,2},MATCH(BigNum,A1:A8),SMALL(IF(ROW(A1:A8)-ROW(A1)+1>MATCH(D1,A1:A8,0),IF(A1:A8="",ROW(A1:A8)-ROW(A1)+1)),1)-1))),2,0)

...where D1 contains Zelda, and E1 ccontains 10/1/06. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Upvote 0
Domenic,

Your formula seems quite complicated, however if I am not mistaken it seems that the formula is only set to pick up those two data ranges (Zelda and Mario). I assume this is the case because the choose function only has 1 & 2. Would this work if I had many 100+ tables to pull data from?
 
Upvote 0
Your formula seems quite complicated...

Yes, unfortunately the data is not laid out in a conventional manner...

...however if I am not mistaken it seems that the formula is only set to pick up those two data ranges (Zelda and Mario). I assume this is the case because the choose function only has 1 & 2.

Actually, that's not the case...

Would this work if I had many 100+ tables to pull data from?

Yes. Adjust the ranges accordingly. For example, if A1:B100 contains the data, change A1:A8 and B1:B8 to A1:A100 and B1:B100.
 
Upvote 0
As Domenic has already pointed out, your data are organized in a less than optimal fashion. Reorganize the data in a table with columns: Name, Date, NbrDownloads. Now, you can use many of the tools provided by XL, including PivotTables, to analyze your data.
Hey everyone,

This is my first post in this forum. I am using Windows XP and Excel 2002. The following are the tables that I am working with. I am trying to set up an array lookup that will work even as dates are added. I have tried an index function with match functions to try and get this to work, but no success. What I would like to do for example is search the whole range for Zelda on 10/1/06 and get the appropriate number of downloads. I hope this is clear. Thanks in advance for your help
{snip}
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top