Home |
Search |
Today's Posts |
#1
|
|||
|
|||
DGet vs VLookup
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 |
#2
|
|||
|
|||
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,Databaserea d,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. |
#3
|
|||
|
|||
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,Databaserea d,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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DGet vs VLookup
Hello .....sorry but I see that the formulña dget from 123 dos not work in
Excel li in 123? ..... "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,Databaserea d,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
At my wit's end! Use DGET or VLOOKUP to select data???? | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |