You can use Google Sheets D MAX function to find the optimum value in an array based on some standards. By way of example, if you’ve got a table of information with obligations that are out standing you might be owed for every one of your clients, it is possible to use DMAX to discover the earliest of the out standing repayments for each customer.
In my example, I will be using this source data in Sheet 2.
I want to create a summary in Sheet 1 where I look up the oldest outstanding payment that each client owes me.
1.First, I need to set up my table in Sheet 1. I can set up my list of client names by typing them into column A or by using the unique function.
Kind in the subsequent:
Then use your mouse to highlight the whole database of your source information which has the posts you will end up utilizing – including headers. Inside my situation, it’s Sheet2!a 1:F 12. Should you be going to be duplicating this method down (a si am to appear up all customers), then hit F4 in your keyboard to alter this from a family member to complete method, that may appear to be Sheet2!$A$1:$F$1 2.
3.Type in a comma, and then type in field or the column header name from the databasefrom which you want to return values. Include quotes around the text. In my case, that is “Age”.
4 Variety in a comma, accompanied by fluorescent brackets . The standards employed to type through the maximum may move inside of the brackets that are curly. First comes the standards to which I would like to examine it, after which the database line, a semi colon. Inside my situation, which is ; a 2. (A2 is the blocked customer title in Sheet 1 near where I will be typing in the method.) Near the parenthesis, hit enter, and the optimum age will be seen by you for the customer that fits a 2.
5 Click on cell B2, and then click on the bottom right corner and drag it down to B5. This will copy the formula into those cells to check for the other clients. Because you used an absolute reference for the database in Step 1 and a relative reference for the criteria in Step 4, you do not need to change anything with the formulas.