DGet vs VLookup

[earth21994]

I am trying to convert a Lotus file over to Excel, and am having some trouble
converting an error handling dget function.

=IF(ISERR(DGET(Databaseread,"Name","GROUP
ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber))

This is the function that was used in Lotus; it returns the name of a
company by looking at the ID number. I need to keep it as pure as possible to
the Lotus file.
Any help would be appreciated- thanks!
--
earth21994

[Harlan Grove]

earth21994 wrote...
>I am trying to convert a Lotus file over to Excel, and am having some trouble
>converting an error handling dget function.
>
>=IF(ISERR(DGET(Databaseread,"Name","GROUP
>ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber))
>
>This is the function that was used in Lotus; it returns the name of a
>company by looking at the ID number. I need to keep it as pure as possible to
>the Lotus file.
....

I don't want you to get your hopes up - YOU'RE DOOMED!

Lotus 123's @DGET (and other database functions) are much more
sophisticated than Excel's counterpart functions. 123's can use
criteria expressions in the function calls. Excel's require criteria
ranges.

In this particular case, there's no need to use DGET at all. There's a
single criterion term, so VLOOKUP is sufficient. If the "Name" column
were the 4th column in Databaseread, then try

=VLOOKUP(GroupNumber,Databaseread,4,0)

Explanation: it appears you're just trying to find a particular group
number. DGET (and @DGET in 123) returns an error if there's more than
one entry. VLOOKUP returns the first matching entry. You're formula
makes it clear you want either the only matching entry or the first
matching entry. However, when there's only one matching entry it's also
the first matching entry, so VLOOKUP alone would have returned the
desired result.

I suspect you have other formulas that are more complicated, but you
believed the formula above would be a reasonable sample to provide. Not
so. If you have more complicated D-function calls, show them, not the
simple ones.

[earth21994]

Thank you for the help. That was pretty much the difficult D function to
figure, because I could not figure out how to put it into an array like the
other ones. Now I will try to figure out how to test for multiple instances
of a name in a database. Thank you very much!
--
earth21994


"Harlan Grove" wrote:

> earth21994 wrote...
> >I am trying to convert a Lotus file over to Excel, and am having some trouble
> >converting an error handling dget function.
> >
> >=IF(ISERR(DGET(Databaseread,"Name","GROUP
> >ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber))
> >
> >This is the function that was used in Lotus; it returns the name of a
> >company by looking at the ID number. I need to keep it as pure as possible to
> >the Lotus file.
> ....
>
> I don't want you to get your hopes up - YOU'RE DOOMED!
>
> Lotus 123's @DGET (and other database functions) are much more
> sophisticated than Excel's counterpart functions. 123's can use
> criteria expressions in the function calls. Excel's require criteria
> ranges.
>
> In this particular case, there's no need to use DGET at all. There's a
> single criterion term, so VLOOKUP is sufficient. If the "Name" column
> were the 4th column in Databaseread, then try
>
> =VLOOKUP(GroupNumber,Databaseread,4,0)
>
> Explanation: it appears you're just trying to find a particular group
> number. DGET (and @DGET in 123) returns an error if there's more than
> one entry. VLOOKUP returns the first matching entry. You're formula
> makes it clear you want either the only matching entry or the first
> matching entry. However, when there's only one matching entry it's also
> the first matching entry, so VLOOKUP alone would have returned the
> desired result.
>
> I suspect you have other formulas that are more complicated, but you
> believed the formula above would be a reasonable sample to provide. Not
> so. If you have more complicated D-function calls, show them, not the
> simple ones.
>
>