Dget Formula In Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.


Dget Formula In Excel






“Extracts a single value from a column of a list or database that matches conditions that you specify.

Syntax

DGET(database,field,criteria)

Database is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as “”Age”” or “”Yield,”” or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Criteria examples
Important
Because the equal sign is used to indicate a formula when you type text or a value in a cell, Microsoft Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:=”=entry”
Where entry is the text or value you want to find. For example:
What you type in the cell What Excel evaluates and displays
=””=Davolio”” =Davolio
=””=3000″” =3000
When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see Filtering for text by using a case-sensitive search.The following sections provide examples of complex criteria.
Multiple criteria in one columnMultiple criteria in multiple columns where all criteria must be trueMultiple criteria in multiple columns where any criteria can be trueMultiple sets of criteria where each set includes criteria for multiple columnsMultiple sets of criteria where each set includes criteria for one columnCriteria to find text values that share some characters but not othersCriteria created as the result of a formulaFiltering for values greater than the average of all values in the data rangeFiltering for text by using a case-sensitive search Multiple criteria in one columnBoolean logic: (Salesperson = “”Davolio”” OR Salesperson = “”Buchanan””)
To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range.
In the following data range (A6:C10), the criteria range (B1:B3) displays the rows that contain either “”Davolio”” or “”Buchanan”” in the Salesperson column (A8:C10).
A B C
1 Type Salesperson Sales
2 =Davolio
3 =Buchanan
4
5
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544
Top of Page
Multiple criteria in multiple columns where all criteria must be trueBoolean logic: (Type = “”Produce”” AND Sales > 1000)
To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range.
In the following data range (A6:C10), the criteria range (A1:C2) displays all rows that contain “”Produce”” in the Type column and a value greater than $1,000 in the Sales column (A9:C10).
A B C
1 Type Salesperson Sales
2 =Produce >1000
3
4
5
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544
Top of Page
Multiple criteria in multiple columns where any criteria can be trueBoolean logic: (Type = “”Produce”” OR Salesperson = “”Davolio””)
To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in different rows of the criteria range.
In the following data range (A6:C10), the criteria range (A1:B3) displays all rows that contain “”Produce”” in the Type column or “”Davolio”” in the Salesperson column (A8:C10).
A B C
1 Type Salesperson Sales
2 =Produce
3 =Davolio
4
5
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544
Top of Page
Multiple sets of criteria where each set includes criteria for multiple columnsBoolean logic: ( (Salesperson = “”Davolio”” AND Sales >3000) OR (Salesperson = “”Buchanan”” AND Sales > 1500) )
To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate rows.
In the following data range (A6:C10), the criteria range (B1:C3) displays the rows that contain both “”Davolio”” in the Salesperson column and a value greater than $3,000 in the Sales column, or displays the rows that contain “”Buchanan”” in the Salesperson and a value greater than $1,500 in the Sales column (A9:C10).
A B C
1 Type Salesperson Sales
2 =Davolio >3000
3 =Buchanan >1500
4
5
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544
Top of Page
Multiple sets of criteria where each set includes criteria for one columnBoolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) ) To find rows that meet multiple sets of criteria, where each set includes criteria for one column, include multiple columns with the same column heading. In the following data range (A6:C10), the criteria range (C1:D3) displays rows that contain values between 6,000 and 6,500 and values less than 500 in the Sales column (A8:C10). A B C D 1 Type Salesperson Sales Sales 2 >6000 <6500 3 <500 4 5 6 Type Salesperson Sales 7 Beverages Suyama $5122 8 Meat Davolio $450 9 produce Buchanan $6328 10 Produce Davolio $6544 Top of Page Criteria to find text values that share some characters but not othersTo find text values that share some characters but not others, do one or more of the following: Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds ""Davolio,"" ""David,"" and ""Davis.""Use a wildcard character. How? The following wildcard characters can be used as comparison criteria. Use To find ? (question mark) Any single character For example, sm?th finds ""smith"" and ""smyth"" * (asterisk) Any number of characters For example, *east finds ""Northeast"" and ""Southeast"" ~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde For example, fy91~? finds ""fy91?"" In the following data range (A6:C10), the criteria range (A1:B3) displays rows with ""Me"" as the first characters in the Type column or rows with the second character equal to ""u"" in the Salesperson column (A7:C9). A B C 1 Type Salesperson Sales 2 Me 3 =?u* 4 5 6 Type Salesperson Sales 7 Beverages Suyama $5122 8 Meat Davolio $450 9 produce Buchanan $6328 10 Produce Davolio $6544 Top of Page Criteria created as the result of a formulaYou can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) as your criterion. Remember the following important points: The formula must evaluate to TRUE or FALSE. Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:=''=entry'' Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match). If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered. The formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row (in the examples below, C7 and A7).All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).The following subsections provide specific examples of criteria created as the result of a formula. Top of Page Filtering for values greater than the average of all values in the data rangeIn the following data range (A6:D10), the criteria range (D1:D2) displays rows that have a value in the Sales column greater than the average of all the Sales values (C7:C10). In the formula, ""C7"" refers to the filtered column (C) of the first row of the data range (7). A B C D 1 Type Salesperson Sales Calculated Average 2 =C7>AVERAGE($C$7:$C$10)
3
4
5
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544
Top of Page
Filtering for text by using a case-sensitive search In the data range (A6:D10), the criteria range (D1:D2) displays rows that contain “”Produce”” in the Type column by using the EXACT function to perform a case-sensitive search (A10:C10). In the formula, “”A7″” refers to the filtered column (A) of the first row of the data range (7).
A B C D
1 Type Salesperson Sales Exact Match
2 =EXACT(A7, “”Produce””)
3
4
5
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544

Remarks

If no record matches the criteria, DGET returns the #VALUE! error value.
If more than one record matches the criteria, DGET returns the #NUM! error value.
You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.
For example, if the range G1:G2 contains the column label Income in G1 and the amount $10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.

Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
Make sure that the criteria range does not overlap the list.
To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.This Excel tutorial has been created to help you learn microsoft excel online. On http://www.myelesson.org you get excel help and online file download facitity. This video lesson covers the entire topic in simple and easy to understand step by step approach. ”








Our Other Services




Read Free Ebooks

myeboook.com

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!



Buy Excel Course

myelesson.org/product

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788



Create Best Looking Resume

http://www.makecv.org/

At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!


Download Excel Crash Course


This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.



Learn Photoshop Web Designing


You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .



Learn HTML CSS


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .


Download Full list of Excel Shortcuts


This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.




Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org




Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.




List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.





Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok
Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok