Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Use formula in dget criteria Results

Hi there,

I'm getting an error with a DGET function. One of the criteria fields
is a formula (an IF statement) which I assume is the problem.

Is there any way round this i.e. being able to change the criteria
based on the contents of another cell?

Many thanks - David

I am a Lotus123 user and have switched to Excel. I cannot get exactly what i want from the DGET function in Excel.
The lotus formula is:
@dget($ProdMaster;"Description";CODE=A10)
I know that there is a DGET function in Excel, but i am confused at the 'criteria' requirement. I used the VLoookup function, but it doesnt give an exact match!
Much obliged if someone can help!

I have a database of information for the products I deal with (i.e. Product
names, Product ID numbers, Product Pricing...). Using the DGET function
formal, I can populate a list of products according to the criteria used in
DGET. I was wondering is there a formula or way to identify duplicate
entries within a field (i.e. - Product ID numbers). Some products use the
same accessories and I want to limit the list of accessories (or at least
identify the duplicates) so that I can isolate the accessory one time without
having it listed several times. All I need is help identifying duplicates
within the field/range and I can take it from there. Thank you all for any
help you can give me.

I have a dget function that I want to use different range and criteria ranges
depending on a flag. I thought that index would work but I am getting a
value error. Thanks in advance for any help.

Here's the formula:

=dget(index(rangearray,1),az11,index(rangearray,3))

whrere
Med1_602
Med2_602
Med1_602Crit
Med2_602

are in a range called rangearray. These are all ranges that I want to use
in the dget formula depending on other criteria.

If I do the index function independently it gives me the correct range but
in the dget formula I get an error?

Thanks, I posted this last week on the function area, but didn't receive a
solution, hoping someone here know one.

Thanks in advance for any help.

I have a dget function that I want to use different range and criteria ranges
depending on a flag. I thought that index would work but I am getting a
value error. Thanks in advance for any help.

Here's the formula:

=dget(index(rangearray,1),az11,index(rangearray,3))

whrere
Med1_602
Med2_602
Med1_602Crit
Med2_602

are a range called rangearray

If I do the index function independently it gives me the correct range but
in the dget formula I get an error?

To anyone that has encountered this issue, (see attached file)
I am trying to use the database DGET function and I continue to get the error result #NUM! which according to Excel help indicates duplicate values in the database. I reviewed the database by using auto filter on the criteria I entered for the DGET function and only 1 record is returned. So, how can there be a duplicate? I have also had bad results using the DSUM function especially when the criteria has similar items in the field but not a redundant item. Any help would be much appreciated.

Hi Guys-

I am REALLY hoping you can help me out here. I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel. Any suggestions? Thank you sooooo much

I am using a loop to redefine criteria for a DGET worksheet function for each part in a list.

However, in order to have DGET recognize criteria, the cell needs to appear:
=partnumber
But since = signs automatically try to interpret the following as a formula I need to enter into the cell:
="=partnumber"
(the partnumber is a variable thats redefined on each loop)

But VBA won't enter the equal signs/quotes into the cell before and after the part number as shown because the signs are being interpreted as my code.

The following code is the line that will not enter what I want into the cell within my defined "CriteriaRange" that I refer to in my DGET function.


	VB:
	
CriteriaRange.Cells(2, 1).Value = "=" & """ & " = " & sItem & """ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This didn't work either because VBA interpreted it as a TRUE/FALSE test whether the = sign was = to the partnumber (always false)


	VB:
	
CriteriaRange.Cells(2, 1).Value = "="=" & sItem & """ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Basically, I can't find a way to code VBA to enter quotes and equal signs into a cell along with a string defined by a variable because quotes and equal signs are themselves part of VBA code.

Hope theres a simple fix.
Thanks again

When using the dget function how do you list mulitple returns that match the criteria.

for example if 223 is the max and three names match 223 what would be the best way to list them in a spreadsheet

Does anybody know how I can lookup multiple criteria in the same way as VLOOKUP except I need the syntax to be LOOKUP(values,table array columns 2-4,column number).

My data is in the following format

Ref 1 Field 1 Field 2 Field 3 Output
Ref 2 Field 1 Field 2 Field 3 Output
Ref 3 Field 1 Field 2 Field 3 Output

where I want the output coumn to look across Field1..Field 3, and match all of them to the first 3 columns of an array, and return the value in the designated column number. Tried the database functions (DGET,DSUM etc) but they require the headers in the formula which only works for the first row.

Hello everyone,

I believe I didnt state my problem correctly in my original post. I am converting array formulas by using dget (which i have read would be faster). I have all my criteria coming in right. the only problem I have in solving is I have one column with multiple portfoilio numbers and another with portfolio names. Soooo, I could have several portfolio numbers to one portfolio name.

My dget is based off of portfolio names so does anyone know how i can incorporate some sort of match or index where i can make all this work w/o an array?

Thanks in advance for your help.

Latigo

I am wanting to replace some VLookup formulas with the DGet function but cannot get the DGet to work. The syntax seems simple enough but all I get is #N/A errors. The criteria exist in the database range, are not duplicated and I am trying to retrieve the value from the second column of the database range as such:

=DGET(E6:F500,2,I2:I500)

A possible complexity is that the database range is actually part of a pivot table and that I cannot use headings for the columns, or is it simply that you cannot use DGET when your criteria is a long list rather than just a heading and one criteria value?

Any suggestions would be appreciated.

From John Walkenbach's Excel 2002 Power Programming with VBA, I finally found a way to effectively pull a number from a database that matches criteria using an array formula:

{=SUM((DB_VARIABLE=$CB$51)*(DB_YEAR=CA$5)*(DB_JANVALUES)}

What I was wondering about the array formula is whether there is a way to avoid having to name a separate array for each month, as well as DB_VARIABLE and DB_YEAR, for a total of 14 arrays.

(In 1-2-3, I can use the @DGET function to do this putting the criteria directly into the function and all I have to do is name the database range with column headings in one step.)

Or can anyone think of a better way to obtain the value I want from the database using Excel?

OK...I've tried everything I know of. I have two worksheets in a
spreadsheet. On worksheet X, there are two columns and 1100 rows. Column A
is a list of store numbers sorted in ascending order, but they are not
sequential. Column B is the corresponding name of the store. On worksheet
Y, I want to type in the number of a store in a long list of rows under
column A and return the name of the store in the same row in column B.

I can get the VLOOKUP formula to work, but with this problem: if I type in a
store number on worksheet Y that does not appear at all on worksheet X, it
will return a store name anyway. I want to make sure that a store number
entered on worksheet Y is valid, otherwise, return an error.

DGET works perfectly to detect an error, but it will not work in a row by
row format; I have to have a column heading and a single cell below for the
criteria (store number)
--
Allen

See attached pdf.

I want to use a formula to populate the table in the "extract" tab from values in the "cardiac output" column from the "EchoTest" tab based on the criteria that (!Echotest[PT]=!extractA3) AND (!EchoTest[Corrected Vector]=!ExtractB2)

Does that make sense?

I can't use VLOOKUP and I'd hoped DGET would help me but I can't see how to make it do what I'm asking.

Any help here would be greatly appreciated, I'm losing hours of my life and frustration levels are peaking!

Thanks

Marcus

Hi folks,

Here's my situation. I have a database with multiple columns. I am interested in retrieving Column D's information only when Columns J contains text "Enrolled" AND Column U contains "0". The results (any value in Column D where this is true) would be presented in the form of a list.

The goal of this formula would be a sort of quality control measure. Any ideas? I've thought of using the Index formula or the DGet formula, but not sure if either one would fit this scenario.

Thanks,
Kevin

Hello everyone. I'm trying to create a dynamic top 10 list which includes some conditional statements. I've got 4 columns of data:

col A: group identifier (e.g., A, B, C)
col B: unique name/ID
col c: score
col d: rank -- generated using {=(COUNTIF($A$2:$A$254,$A2)-SUM(IF(($C$2:$C$254>=$C2)*($A$2:$A$254=$A2),1,0))+1)}

As you can see, the rank is generated dynamically such that I can add new records to the bottom of the list, and all ranks will be updated. Also note that rankings are within each group. For example, there is a number 1 ranking for group A, as well as a number 1 ranking for group B.

Alright, thats the data setup; onto the report I'd like to generate. I'm trying to generate a 3 column report:

col f: group (an identifier for my conditions)
col g: ranks (will eventually be 1-10)
col h: name/ID of the record meeting the criteria of a particular group (specified in col f) and a particular rank (specified in col g).

My goal is to dynamically output in column H the name of the record which has the rank specified in column G within each grouping that is specified in column F. Thus, cell H2 needs to return ID10, because this record has a rank of 1 for group A. In the same way, H3 should return ID5, H4 should return ID9, etc.

So far, I've played around with vlookup and dget without success because I can't figure out how to include conditions in these statements via nested formulas. I've also contemplated a pivot table, but I'm not sure how to make that work.

If anyone has advice, I'd really appreciate your assistance.

Thanks,

Ryan

------------------------------------------------------

Here is a visual representation of what I currently have:

col Acol Bcol Ccol Dcol Fcol Gcol HGroupNameNumberRankGroupRankNameCID1110352A1CID2208873A2AID3516896B1DID4530004B2AID5103432C1DID6412222C2CID73001D1DID8204351D2BID9113041AID1090001AID11125463AID12234784DID13489323BID14220092AID15406575BID16513433DID171000005AID18768597BID19705574

Hi,

I would like to know how to retrieve one value from a database list based on
multiple criteria. My list as the following headings:

Company, Year, Revenue ($)

I would like to perform some form of lookup function to retrieve a unique
value based on several criteria.

E.g. If Company = "company 1" and Year = "2000", return the value for
"Revenue ($)".

Bear in mind that there are several hundred company names, and that I would
like to be able to copy this formula into other cells, referencing different
criteria.

I've tried DGET, but can't work out how to use.

Any suggestions would be much appreciated.

RA


No luck finding an answer? You could always try Google.