+ Reply to Thread
Results 1 to 15 of 15

How to extract "large" and "small" amounts from vlookup

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to extract "large" and "small" amounts from vlookup

    I have an excel workbook that does lots. I am currently trying to do the following:
    1. summarize values in one worksheet based on a pull down value in another. The sumif works.
    2. determine the highest value of a list of values based on that pull down value.
    3. determine the lowest value greater than 0 of a list of values based on that pull down value.
    4. determine the average value for each category.

    I have attached the workbook and the "Summary" worksheet is where I am reporting these values.

    Cell C7 (DM) is the pulldown cell. All of the data is pulling from the Accounting List worksheet. If I filter by DM column on the Accounting list and check the high (large), low (small), and average, I get different numbers. I tried doing a vlookup but could only return the first number that met the condition and couldn't get it to find the smallest, largest, or average of the vlookup range.

    Any help will be greatly appreciated.
    Attached Files Attached Files
    Last edited by okjeep; 04-23-2012 at 11:06 AM. Reason: specifying the issue

  2. #2
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to extract "large" and "small" amounts from vlookup

    Bump for revised description of the issue.

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: How to extract "large" and "small" amounts from vlookup

    i have attached a book look at the way i have done it and see if it helps

    sorry i haven't done it with your information but i don't have the time to fully understand what your sheet is trying to do.

    i have done a match value and a vlookup value

    and fist second and third highest or lowest values. hope that helps.


    match.xlsx
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  4. #4
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to extract "large" and "small" amounts from vlookup

    Thanks for the attempt. The two sets of formulas didn't work for my condition. I am using a pull down that once the name is selected, the vlookup will then find the large and small of the specific column based on that value (name).

  5. #5
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to extract "large" and "small" amounts from vlookup

    Try this

    Cell F21 =MAX(IF('Accounting List'!$F:F=$C$7,'Accounting List'!$Z:Z)) confirmed with CONTROL+SHIFT+ENTER. Then copy down. Make sure change $Z:$Z to $AA:$AA so on.

    Cell G21 =MIN(IF('Accounting List'!$F:F=$C$7,'Accounting List'!$Z:Z)) confirmed with CONTROL+SHIFT+ENTER. Then copy down. Do the same as Max.

  6. #6
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to extract "large" and "small" amounts from vlookup

    This formula returns the min and max of the column but it won't sort it by the pull down value even with the "=C7". This was one of my first attempts before thinking about either match or index, both of which the syntax eludes me. The brick wall is beginning to hurt my head.

  7. #7
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to extract "large" and "small" amounts from vlookup

    Hi.

    It work on my computer workbook. I don't understand as you looking for name with high or low number. Have you done control+shift+enter?

  8. #8
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to extract "large" and "small" amounts from vlookup

    Ah-ha! The old "control/shift/enter" that makes the {} do-hickeys! This worked! Last challange of the day, I will need min that is greater than 0 so it doesn't return the "$0.00". Also, what are the {}'s?

  9. #9
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to extract "large" and "small" amounts from vlookup

    Ok

    Min? Will work on that. I will be offline. Check back next 24 hour. unless someone got it.

    Cheers

  10. #10
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: How to extract "large" and "small" amounts from vlookup

    try extending micope21's formula to:

    =MIN(IF(('Accounting List'!$F:F=$C$7)*('Accounting List'!$Z:Z>0),'Accounting List'!$Z:Z))

    and do the curly-do-hickey stuff...
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  11. #11
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to extract "large" and "small" amounts from vlookup

    Is there a way to add the "{" and "}" in the syntax for the control+shift+enter so that if i affect the formula, or if someone else affects the formula that it will be there automatically?

  12. #12
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to extract "large" and "small" amounts from vlookup

    Hi

    Went out now back home online. I solved it on excel and icestationzbra got it same formula as I did.
    cell G21 =MIN(IF(('Accounting List'!$F:F=$C$7)*('Accounting List'!$Z:Z>0),'Accounting List'!$Z:Z)) CONTROL+SHIFT+ENTER.

    That what icestationzbra said "do the curly-do-hickey stuff..."


    If you happy? Please click solved, please say so clearly, and mark your thread as Solved:
    Forum Rules & How to Mark a thread as SOLVED
    Good luck.

    Cheers

  13. #13
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: How to extract "large" and "small" amounts from vlookup

    Unfortunately you can't have the braces, { }, automatically entered without using CTRL+SHIFT+ENTER or a macro. I'd suggest adding a note to that field for users letting them know they must use CSE if they edit the cell.

  14. #14
    Registered User
    Join Date
    04-18-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to extract "large" and "small" amounts from vlookup

    Thanks everyone for your assistance. this thread is solved. I'm sure I will have plenty more questions on new topics as I delve more into excel!

  15. #15
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: How to extract "large" and "small" amounts from vlookup

    @okjeep if you have found the solution, please mark the thread as solved from the drop-down menu "Thread tools" at the top of the page.

    Thanks.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1