Can DGET replace INDEX(MATCH)?

[Art B.]

My workbook takes 22 hours to calculate. From reading posts at this site I understand that if I can replace array formulae (which I’ve used liberally) with EXCEL database functions, calculation will be faster. Toward this objective, can I use DGET or another non-array function to fill “x” in the “Results” worksheet below with the appropriate datum from the “Eggs” field in the “Nest ID” table? (The first two fields in the “Results” sheet are entered manually).

“Nest ID” worksheet:

Date Nest Eggs
5-Jun-01 1 4
5-Jun-01 2 2
6-Jun-01 1 3
6-Jun-01 2 1

“Results” worksheet:

Date Nest Eggs
6-Jun-01 2 x

Here’s the array formula I’ve used to get “x”:

=index(Nest ID!c2:c5,match(a2&b2,Nest ID!a2:a5&Nest ID!b2:b5,0))

Thanks!

P.S. How does one produce tables in posts?

[ This Message was edited by: Art B. on 2002-05-20 14:53 ]

[Yogi Anand]

On 2002-05-20 14:43, Art B. wrote:
My workbook takes 22 hours to calculate. From reading posts at this site I understand that if I can replace array formulae (which I’ve used liberally) with EXCEL database functions, calculation will be faster. Toward this objective, can I use DGET or another non-array function to fill “x” in the “Results” worksheet below with the appropriate datum from the “Eggs” field in the “Nest ID” table? (The first two fields in the “Results” sheet are entered manually).

“Nest ID” worksheet:

Date Nest Eggs
5-Jun-01 1 4
5-Jun-01 2 2
6-Jun-01 1 3
6-Jun-01 2 1

“Results” worksheet:

Date Nest Eggs
6-Jun-01 2 x

Here’s the array formula I’ve used to get “x”:

=index(Nest ID!c2:c5,match(a2&b2,Nest ID!a2:a5&Nest ID!b2:b5,0))

Thanks!

P.S. How does one produce tables in posts?

[ This Message was edited by: Art B. on 2002-05-20 14:53 ]

You can use the following DGET function ...

=DGET(A1:C5,"Eggs",E1:F2)

where your data is in cells A1:c5, and the criteria is in cells E1:F2
where E1 houses Date
where E2 6-Jun-01
F1 houses Nest
F2 houses 2

[Aladin Akyurek]

On 2002-05-20 14:43, Art B. wrote:
My workbook takes 22 hours to calculate. From reading posts at this site I understand that if I can replace array formulae (which I’ve used liberally) with EXCEL database functions, calculation will be faster. Toward this objective, can I use DGET or another non-array function to fill “x” in the “Results” worksheet below with the appropriate datum from the “Eggs” field in the “Nest ID” table? (The first two fields in the “Results” sheet are entered manually).

“Nest ID” worksheet:
[code]
Date Nest Eggs
5-Jun-01 1 4
5-Jun-01 2 2
6-Jun-01 1 3
6-Jun-01 2 1

“Results” worksheet:

Date Nest Eggs
6-Jun-01 2 x
[code]
Here’s the array formula I’ve used to get “x”:

=index(Nest ID!c2:c5,match(a2&b2,Nest ID!a2:a5&Nest ID!b2:b5,0))

Thanks!


[ This Message was edited by: Art B. on 2002-05-20 14:46 ]

[ This Message was edited by: Art B. on 2002-05-20 14:50 ]

I don't think DGET can replace your array-entered INDEX/MATCH.

You might however try trading off space against time as it is called.

In your data sheet, insert a new column next to the Nest column and use that as column to look in with INDEX/MATCH ordinarily entered.

Here is how your Nest ID sheet will look.

Microsoft Excel - aaIndexMatch ArtB.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = Date
	A	B	C	D
1	Date	Nest		Eggs
2	5-Jun-01	1	:alert('=A2&%22@%22&B2')>37047@1	4
3	5-Jun-01	2	:alert('=A3&%22@%22&B3')>37047@2	2
4	6-Jun-01	1	:alert('=A4&%22@%22&B4')>37048@1	3
5	6-Jun-01	2	:alert('=A5&%22@%22&B5')>37048@2	1
Nest ID

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.26]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Note that the Results sheet does not need to use array-entered INDEX/MATCH formulas.

Microsoft Excel - aaIndexMatch ArtB.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = Date
	A	B	C	D
1	Date	Nest	Eggs
2	6-Jun-01	2	:alert("=INDEX(%27Nest%20ID%27!$D$2:$D$5,MATCH(A2&%22@%22&B2,%27Nest%20ID%27!$C$2:$C$5,0))")>1
3
Results

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.26]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


I believe DGET would require you to have a huge table of criteria in a separate worksheet in order to work. The concatenation of fields, mind you, is also required for DGET.

Aladin

Addendum. As Andreas notes, devising a pivot table would be a good idea.



[ This Message was edited by: Aladin Akyurek on 2002-05-20 15:25 ]

[sen_edp]

Hello Art, Hello Yogi

I made an example with Art's Data , and another solution is to use pivot tables , where you can choose dates,nest and you can group dates too.

[Yogi Anand]

On 2002-05-20 15:13, sen_edp wrote:
Hello Art, Hello Yogi

I made an example with Art's Data , and another solution is to use pivot tables , where you can choose dates,nest and you can group dates too.

Hi Andreas:
...
and instead of extracting just one value, you can view the entire summary of results --a very powerful tool indeed!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:08 ]