1. N

    How to open one workbook find a max value in one worksheet and return it from a function

    Full disclaimer I have only been using VBA for very small projects and have never attempted working with multiple workbooks. I have a user form in 365 that enters data per the form's request. I am using 3 files, an entry file, a database file, and a read file. Between the database file and...
  2. D

    DAX ; how MIN / MAX see data model

    Hi, this is more a a theory question than practical; in the measure ; Tbetween:=CALCULATE([TotalUnits], FILTER(ALL(Table1[Units]), Table1[Units] <= MAX(Table3[Max]) && Table1[Units] >=...
  3. N

    Vlookup max value and finding number in specific column

    Hi, thanks for help. 1. I am trying to find a max number in a vlookup but my formula is not working (see below) 101 101 3 96 78 So if im trying to find 101 in the table i would do =VLOOKUP,101,table,3,false) but it will bring up 78 but what i need it to...
  4. B

    VLOOKUP and Max Function to return multiple values

    I'm trying to see which day of the week had the most sales each month. Column A is # of Sales. I'm currently using this formula =VLOOKUP(MAX(A$44:A$50),A$44:B$50,2,FALSE) Because this formula can only return one value, it will return "Sunday" since it is the first in column B with highest...
  5. P

    Compare tables and find closed date prior to selection

    Hi All, I'm currently doing scientific work and i can't find a way to find to match 2 table to find the closest date prior to the selection. I was wondering whether you experts can help me. As the data i'm working with is confidential i'm making a dummy table to show what i would need. What...
  6. D

    MAX value best practice

    Hi When trying to find the maximum value with a filter condition is there any difference between these two methods and which if either is regarded as better; ; MAX( Table1[Units] ) Then, CALCULATE([MaxU],(FILTER(ALL(Table1[Item]) ,Table1[Item]= "A") ) or...
  7. M

    Lookup the maximum date of a record and return the result of a corresponding column

    I have two tables - Parent table listing asset numbers, and another table with records of services for that asset. I would like to lookup the asset number, search the maximum (last date) it was serviced in the records table, and return the comment that is in another column eg: Thank you :)
  8. P

    I have a given range and trying to get the max value of corresponding column as per given range

    Im really not sure how to explain this but here goes, Starting from the right, as u can see each item has specific range of KP (from column K and L). We start with item 1 for example, which range from 0.211 to 0.216. Now i would like to match these range to the ones in column A. Once that...
  9. A

    Countifs with filter dates

    Hi, I have a table like the one below. User Current Stage Stage 1 Date Stage 2 Date Stage 3 Date A Stage 2 15/2/2022 16/4/2022 B Stage 1 10/1/2022 C Stage 3 22/5/2022 26/6/2022 28/6/2022 D Stage 1 14/4/2022 16/4/2022 E Stage 2 15/1/2022 16/2/2022 18/4/2022 I am trying...
  10. U

    Need help finding the account with the lowest and highest value from several sheets.

    I am trying to return the account number with the lowest and highest values. I tried to use this formula to return the lowest value. =MIN(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3) =MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3) Account # Column 206-1...
  11. H

    Aggregate Max function not working correctly

    I have a sheet which keeps a tally of profit, drawdown and the like and I am trying to do a simple calculation, but the answer seems way off. In column CU, drawdown % is calculated. At present there are only 59,200 rows and row 22 is the first row of data. This is the formula to work out the...
  12. C

    IF/MAX/MATCH formula required

    A B C D E F G H 1 week 1 week 1 week 2 week 2 week 3 week 3 2 W R W R W R WB RB 3 25 10 30 8 30 12 30 FIND THIS NUMBER I have a table of data that looks like above. I have used MAX function to find the highest occurring number in cells labelled with "W". In cell H3, I want to find...
  13. jase71ds

    An Elegant Solution to Setting Evaluation Constraints

    This isn't a question - just wanted to share a solution I came up with. Others have probably come up with the same solution, but I was pleased with myself for figuring it out on my own. Plus, it was fun! If I want to constrain a formula output, say between +100% and -100%, one popular way to do...
  14. A

    Use of LARGE, SMALL, MAX, MIN, SUM Functions in spilled arrays

    Hi all, I would like to use functions like LARGE, SMALL, MAX, MIN and SUM in spilled arrays and get the values PER row instead of the total. For Example: x y =MAX(A2:B2) dragged down =MAX(A2:B5) 1 2 2 8 3 4 4 5 6 6 7 8 8 The only solution I...
  15. M

    Replacing OFFSET to sum up to max value from specific cell

    Hello everyone, I am new to the board & thank you all in advance for the help. I have data for the time it takes each step in a manufacturing process & each step has a unique name that never repeats, as seen below, pasted in A1 corner. Steps time Start Point Sum of Steps Count 2800N1 5...
  16. U

    Return Max Value of Column X after Vlookup of Column A

    Hello, I have a workbook with 2 sheets, "ProgramMasterList" and "urslabs". I'm trying to lookup the latest Date in column M in sheet "urslabs" and match the values into sheet "ProgramMasterList" in column V using column A as the lookup value in both sheets. I'm using a vlookup to lookup the...
  17. A

    Returning multiple Max results

    Hi wondering if anyone could help? I have a report on questionnaires I'm doing where I have to return the MAX and MIN values of the results. I first started using the =VLOOKUP(MAX(C4:C12),C4:D12,2,0) formula but it only returns the first MAX value to me but instead I want a formula that can...
  18. M

    Return salesperson with maximum sales per client

    Hi, thanks for reading this post! (1) I have a table with the data, like this: Client Salesperson Sales ($) Google Mike 1,000 Apple Jim 5,000 Apple George 4,000 Apple George 3,000 ... ... ... (2) On another sheet, I'm creating a report, like this: Client Frequent/Max...
  19. S

    Find more than one maximum value before a zero

    Hello everyone I'm trying to use the formula =MAX(FREQUENCY(IF(T2:T11>1,COLUMN(T2:T11)),IF(T2:T11>=1,COLUMN(T2:T11)))) to find more than one maximum value before a zero from a range. Column T has this values 1 2 0 1 2 3 4 5 6 0 I want a formula/formulas that will...
  20. A

    Get MAX value and Other Value based on Criteria

    I have a table (pasted below) showing COMPANY, BRAND, VALUE. I am trying to put a fomula in E2 that looks at the company name in E1, then looks in the table and picks the brand AND the value for ONLY the MAX for the company in E2. So, E2 should come up with BRAND3: 30 F2 should come up with...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back