Excel database lookup solution

[VegasNath]

Good morning,

I have built an excel database which consists 71 metrics x 1,098 dimensions = 77,958 unique rows. The data was previously managed across >1000 tabs in numerous different workbooks, so as mad as this may sound (appreciate Excel is not ideal for this but is a compulsory requirement), this solution is progress!

I have then built on another tab a look up tool to query the data which works fine if the data you require is towards the top of the 80k row range, but becomes far to slow for the lower data.

I use a data validation list for the 1,098 dimensions to be selected, which the look ups use.

I am trying to find a speedier solution and would appreciate your thoughts on the following approach;

Use a cell change event on the data validation list that FINDs the first row in the database of the selected dimension, outputting the row number to a cell (not sure if this would still be slow?)

Change the VLOOKUP formulas to use this row number as the starting point, add 71 for the ending point (Is this possible?)

Before I go to far along the wrong path, I would appreciate if you could advise on whether this would work, or advise on an alternative? Thanks in advance.

[HansV]

Perhaps you could use a dynamic named range, using MATCH and OFFSET or INDEX. The evaluation of named ranges is pretty efficient.

Without knowing the layout of your worksheet it's difficult to provide detailed instructions, but it could be among the following lines:

=OFFSET(Sheet2!$A$1,MATCH(Sheet1!$B$2,Sheet2!$A:$A,0)-1,0,COUNTIF(Sheet2!$A:$A,Sheet1!$B$2),6)

This means:
OFFSET(Sheet2!$A$1: Start from Sheet2!A1
MATCH(Sheet1!$B$2,Sheet2!$A:$A,0)-1: Go down until you find the value of Sheet1!B2 (the cell with data validation)
0: Don't go to the right
COUNTIF(Sheet2!$A:$A,Sheet1!$B$2): Expand to the number of rows that match Sheet1!B2
6: Expand to 6 columns

Modify to match your layout.

[VegasNath]

Thanks for the help Hans.
This is my current formula which I need to translate;
=VLOOKUP($F$6&"_"&$F8,Database!$A$1:$EZ$80672,COLUMN(),0)

But am struggling. I need to be able to drag across and down

[VegasNath]

Column E is spare to hold the offset value if required

[HansV]

I'm afraid that doesn't help much. I'd have to see the workbook, or have much more detailed information.

[VegasNath]

Unfortunately unable to post from my phone :(

Database is laid out with unique identifiers in Col a, data starts in h, blocks of 71

Lookup formulas start in h to correspond with same Col as database, validation in f6, numeric value of required row (1:71) is in e

So Lookup e8 = 1
Lookup H8 formula needs to find validation f6 in database Col a, return the value of the 1st occurance (e8 = 1) and return the VAL of corresponding column h

Hope this is ok

[HansV]

Sorry...

[VegasNath]

Small progress

=OFFSET(Database!A1,MATCH('Q4 Lookup'!$F$6,Database!$A:$A,0)-1,0,COUNTIF(Database!$A:$A,'Q4 Lookup'!$F$6),COLUMN())

This is picking up the first row fine, how do I adapt topick up the row detailed incol e?

[HansV]

Not sure I understand, does this do what you want?

=OFFSET(Database!$A$1,MATCH('Q4 Lookup'!$F$6,Database!$A:$A,0)-1+'Q4 Lookup'!$E1,0,COUNTIF(Database!$A:$A,'Q4 Lookup'!$F$6),COLUMN())

[VegasNath]

Kind of close, I can't work out what the -1 is doing, I just need to get the row number ad detailed in Col e

[HansV]

It's impossible for me to know what you need without seeing the database.

[PJ_in_FL]

Can you add a column with the row index then set up an Advanced Filter to return the result on a separate sheet e.g. Report?

The Advanced Filter will return all entries with the selected dimension, and the first entry below your headers on the Report sheet is the first occurrence in the database.

(I still don't know how to post a picture of a screen shot so bear with my explanation below. I use Excel 2007. YMMV.)

Set up the Report sheet with the criteria and report headers EXACTLY like your headers in the database. I like to put the criteria in the top rows of the sheet, skip a few rows then the headers for the report.

I also like to set up a dynamic range for the database and ranges for the criteria and report headers on the report sheet. Makes filling in the Advanced Filter dialog easier, which seems to be required if you want to put the report on another sheet, or at least that's how it appears to work best for me.

In the Advanced Filter dialog box:
1. Check the "Copy to another location" button
2. Fill in the List Range with your database range using your dynamic range name
3. Fill in the Criteria range (also use the defined name)
4. Fill in the Copy To range with the range defined for your header row on the Report sheet.

[HansV]

I still don't know how to post a picture of a screen shot.

Simply upload the picture as an attachment. If the attachment is a .gif, .png or .jpg file it will be displayed as a picture in the post.
Also see this post.

[Jan Karel Pieterse]

If I inderstand correctly, the VLOOKUP formulas are causing your workbook to calculate very slowly, correct?

Looking at the formula you posted:

=VLOOKUP($F$6&"_"&$F8,Database!$A$1:$EZ$80672,COLUMN(),0)

I conclude you are dragging this formula to the right and downwards to fetch the data.
This means, that for each and every column that contains the VLOOKUP you are asking Excel to run through all the 80,000 rows again to find the item you need.
It is more efficient if you add a column in which you lookup that index number ONCE and subsequently use the (ver fast) INDEX function to fetch the associated data from the found row:
Suppose in cell C2 you have:

=MATCH($F$6&"_"&$F8,Database!$A$1:$A$80672,0)

Then in D2 you might look up the assiciated info from column EA:

=INDEX(Database!EA$1:EA$80672,C2)

If the source data on "Database" is sorted by the lookup column things can be speeded up even more.