# Three Dimensional Lookup Problem

#### tomdabom76

##### New Member
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
310/1/20065
410/2/200610
5
6SuperMario
810/1/20065
Sheet1

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Domenic

##### MrExcel MVP
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
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
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
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

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
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}

Thanks.

Replies
1
Views
172
Replies
4
Views
237
Replies
3
Views
296
Replies
1
Views
399
Replies
6
Views
917

1,171,965
Messages
5,878,517
Members
433,347
Latest member
cynthixie

### 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.

### Which adblocker are you using?

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

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