DGET criteria problem

[PAB]

I am using DGET to search for the greatest price of a product listed in a spreadsheet. Each product will have several days listings and multiple daily listings. My criteria table uses DMAX to retreive the most recent time and date listing from the same spreadsheet. When using products that are similar up to the 15th character I receive a #NUM error message. The specific products are DSL #2 LOW SULF and DSL #2 LOW SULF DYED. Also, when using the FIND function for these products in the spreadsheet column Find Next will stop on both when searching for the shorter product, but when searching for the longer product the search is accurate.
A simple data table is:
Product Price Date
DSL #2 LOW SULF 1.00 6/1/2003
DSL #2 LOW SULF DYED 1.50 6/1/2003
DSL #2 LOW SULF 0.90 6/2/2003 DSL #2 LOW SULF DYED 1.40 6/2/2003
This range is named DATA

A cirteria table of:
Product Date
DSL #2 LOW SULF 06/02/03
This range is named APRODUCT
where date is DMAX(DATA,3,APRODUCT)

Without macros, can I get past the #NUM error message?

 

[Dave Patton]

I am a little lost in your commentary.

DGet will provide the information that meets the criteria
=DGET(A1:C4,B1,E1:F2)
e1:F2 criteria


If you want the Max consider
=DMAX(A1:C5,B1,B2:B4)

For the max for a particular product your may find it easier to use an
Array Formula.

Array enter with Ctrl+Shift+Enter

or

sort your database by Product
then use Subtotal with Max

This will give you the max for each product.

Product is in E5

=MAX(IF(A2:A6=E5,B2:B6))

 

[Mark W.]

Change your 'Product' criteria to that shown below (in yellow)...

Book1
	A	B	C	D	E	F	G
1	Product	Price	Date		Product	Date
2	DSL#2LOWSULF	1.00	6/1/2003		=DSL#2LOWSULF	6/2/2003
3	DSL#2LOWSULFDYED	1.50	6/1/2003
4	DSL#2LOWSULF	0.90	6/2/2003
5	DSL#2LOWSULFDYED	1.40	6/2/2003		0.90
6
7
Sheet3

 

[PAB]

Using the ="=DSL #2 LOW SULF" works in the Product criteria table. Thank you. Now I wish I knew why it will not work without the ="=

 

[Dave Patton]

In the formula that I used, I did not put the = in front of the
text.

If one puts the text under the heading, is that not saying
look for data equal to that text?

 

[Aladin Akyurek]

Excel takes alas a text criterion like DSL #2 LOW SULF as if it is DSL #2 LOW SULF*.

You can circumvent this behavior by resorting to computed criteria...

Book4
	A	B	C	D	E	F	G	H
1	Product	Price	Date		Product		Date
2	DSL#2LOWSULF	1	1-Jun-03		DSL#2LOWSULF	TRUE	2-Jun-03	0.9
3	DSL#2LOWSULFDYED	1.5	1-Jun-03
4	DSL#2LOWSULF	0.9	2-Jun-03
5	DSL#2LOWSULFDYED	1.4	2-Jun-03		DSL#2LOWSULF	2-Jun-03		0.9
6
Sheet1

E1:E2 is the criteria range for the DMAX formula in G2.

F1:G2 is the criteria range for the DGET formula in H2.

F1 is left empty.
F2 houses a formula that represents a computed criteria, that is,...

=ISNUMBER(MATCH($E$2,A2,0))

G2 houses...

=DMAX($A$1:$C$5,3,E1:E2)

H2 houses...

=DGET(A1:C5,2,F1:G2)

What the above system of formulas addresses can also be realized with...

F5:

=MAX($C$2:$C$5)

which determines the max date, and...

H5:

=INDEX($B$2:$B$5,MATCH(1,INDEX(($A$2:$A$5=E5)*($C$2:$C$5=F5),0,1),0))

which retrieves the price that is associated with the max date and the criterion of interest in E5.

 

[Mark W.]

Using the ="=DSL #2 LOW SULF" works in the Product criteria table. Thank you. Now I wish I knew why it will not work without the ="=

If you enter a constant in the criteria it is treated as a substring. So "cat" will find "catalog". However, ="=cat", will only find "cat".

This behaviour is described in the Excel Help topic for "Filter by using advanced criteria"...

All items that begin with that text are filtered. For example, if you type the text Dav as a criterion, Microsoft Excel finds "Davolio," "David," and "Davis." To match only the specified text, type the following formula, where text is the text you want to find. =''=text''

Your DGET function was returning #NUM! because there was more than 1 product that began with the string, "DSL #2 LOW SULF".

There's no need for a computed criteria -- just respect the advice provided in the Help topic.