DGET lookup failing

[Mitch Powell]

I'm having a strange issue with DGET returning #NUM! for a particular
criteria value but not others. The values in the criteria field are as
follows:

Periods: Month & YTD v Budget
Periods: YTD & Year v Budget
Periods: Month, YTD, YTG & Next Yr
Periods: Rolling 12 Months
Periods: Rolling 12 Months plus 2
Periods: Rolling 24 Months
Entities: TIG Consolidated

DGET will successfully return a value when the value in the criteria is ANY
value OTHER than "Periods: Rolling 12 Months". If I use that value in the
criteria it returns #NUM!. It seems to somehow be getting confused due the
presence of another value in the field that is similar ("Periods: Rolling 12
Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to
any other value, DGET will successfully return a value using "Periods:
Rolling 12 Months" as the criteria.

I am really stuumped on this one....

 

[Ashish Mathur]

Hi,

Can you show the data and the formula you are writing

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mitch Powell]

I'll send the data when I get back to the office Tuesday. I can you that I
experimented a bit more and found that it appears that when items in the
criteria field share identiacal first characters (but are different in
total), the function fails because it thinks there are duplicates. For
example, if both Pear and Pears exist in the in criteria column and Pear is
the criteria value, the function returns #NUM!.

 

[Mitch Powell]

The data is as follows:

Field1 Field2
Periods: Month & YTD v Budget ColView_1
Periods: YTD & Year v Budget ColView_2
Periods: Month, YTD, YTG & Next Yr ColView_3
Periods: Rolling 12 ColView_4
Periods: Rolling 12 Months plus 2 ColView_5
Periods: Rolling 24 Months ColView_6
Entities: TIG Consolidated ColView_7

The criteria range is as follows:
Field1
Periods: Rolling 12

DGET returns #NUM!. If I remove the row containing "Periods: Rolling 12
Months plus 2", it works just fine.

 

[Ashish Mathur]

Hi,

Try this. In the criteria range, delete the heading I.e. Field1. In the
next cell of the criteria range, where you have typed Periods: Rolling 12,
enter the following formula

=EXACT("Periods: Rolling 12",B5) where B5 is the first cell (below the
heading) in the range

Now the DGET will return the right answer

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mitch Powell]

It worked! Thanks but I have no idea why it worked. Inquiring minds want to
know...

 

[Ashish Mathur]

Hi,

When you mention the criteria as Periods: Rolling 12, you are in effect
saying that find all cells which contain Periods: Rolling 12. To find for
the exact word, I used the exact function which compares whether the strings
are same or not. Since this formula will evaluate to TRUE/FALSE, I have
given it a heading other then the heading of your source data (Read up on
Database functions in the Help menu).

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com