+ Reply to Thread
Results 1 to 14 of 14

Lookup Query

  1. #1
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Lookup Query

    Hi

    Can anyone help me with a lookup query I have?

    I have a set of data set out similar to below...

    Jan Feb March April
    2 6 8 2


    I have a =MIN formula at the end of the row that tells me the lowest number in that row, which would be 2 in this case. However I would like a formula that also tells me the last month the lowest figure fell in, which would be April.

    It's probably a real easy formula but I'm stuck at the moment, must be a blonde moment!
    Last edited by Alice21; 07-20-2011 at 08:52 AM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    Assuming your data starts in column A this should work although it would need some tinkering if you have it starting in another column:

    =INDEX(A1:D1,SMALL(IF(A2:D2=MIN(A2:D2),COLUMN(A2:D2),""),COUNTIF(A2:D2,MIN(A2:D2))))

    Confirmed with Ctrl+Shift+Enter as it's an array formula.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    As an aside if you listed your data vertically rather than horizontally you could use a simple Lookup formula.

    Dom

  4. #4
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: Lookup Query

    I've tried it but it's not working, just says #value :-/

    The months start in C3:CG3
    The data starts in C4:CG4

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    I'm guessing you didn't confirm it with Ctrl+Shift+Enter rather than just Enter. If you do it correctly you should see brackets like this {} around the formula.

    The formula will be:

    =INDEX(C3:CG3,SMALL(IF(C4:CG4=MIN(C4:CG4),COLUMN(C4:CG4)-2,""),COUNTIF(C4:CG4,MIN(C4:CG4))))

    Dom

  6. #6
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: Lookup Query

    It's giving me the figure '3', but i want it say which month (from cells C3:CG3) the lowest figure fell in.

  7. #7
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    Strange, it works for me.

    Dom
    Attached Files Attached Files

  8. #8
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: Lookup Query

    How odd, my formula looks exactly like that one! I wonder why it's not working? The only difference is that I have actual months not Month 1, 2 etc. But surely that shouldn't make a difference??

  9. #9
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    Can you post a sample workbook?

    Dom

  10. #10
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: Lookup Query

    I think i got it!!

    I need to custom my dd/mm/yy setting to just mm/yy!

  11. #11
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: Lookup Query

    No I don't!

    I changed your example months to actual months then customised the date setting and it worked but when I done it on mine it didn't work! :-(

  12. #12
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    Upload a sample and I'll tell you where it's going wrong.

    Dom

  13. #13
    Forum Contributor Alice21's Avatar
    Join Date
    04-22-2010
    Location
    Wales, United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    173

    Re: Lookup Query

    I've got it! Just had to fiddle with it a bit and sort my format out!

    Thanks sooo much for your help!

  14. #14
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Query

    Cool, glad to help.

    Please mark the thread solved if you're happy with the solution.

    Dom

+ 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