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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Domenic

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

tomdabom76

New Member
Joined
Oct 10, 2006
Messages
10
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?
 

Domenic

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

tomdabom76

New Member
Joined
Oct 10, 2006
Messages
10

ADVERTISEMENT

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?
 

Domenic

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

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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}
 

Forum statistics

Threads
1,136,345
Messages
5,675,220
Members
419,553
Latest member
hanahass

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