+ Reply to Thread
Results 1 to 3 of 3

Finding a minimum value

  1. #1
    Jan Kronsell
    Guest

    Finding a minimum value

    I have a spreadsheet with 7 columns. In clolumns A I have some names, and in
    column B some numbers, fx

    A1 1 100 200 500 800 900
    A1 2 150 300 600 1000 1500
    A1 3
    A2 1
    A2 3
    A2 5 and so on.

    In columns C:G I have some sizes. Only some are show above.

    If I specify fx A1 in I1, 2 in I2 and 458 in I3, I need a formula that
    returns the value 600 from row 2. That is a value that meets the condition
    Name =A1, Number = 2 and size the smallest size, larger than the size
    specified in I3.

    I tried with VLOOKUP in combination with IFs, and I tried with SUMPRODUCT,
    but I have not been able to find a solution so far. Can anybody help?

    Jan





  2. #2
    Bob Phillips
    Guest

    Re: Finding a minimum value

    Jan,

    If you can reverse the order of your sizes, that is 900,800,500,200,10 etc.,
    this will work

    =INDEX(C1:G10,MATCH(I1&I2,A1:A10&B1:B10,0),MATCH(I3,INDIRECT("C"&MATCH(I1&I2
    ,A1:A10&B1:B10,0)&":G"&MATCH(I1&I2,A1:A10&B1:B10,0)),-1))

    this is an array formula, so commit with Ctrl-Shift-Enter

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Jan Kronsell" <span@nospam.no> wrote in message
    news:OTAxWNENFHA.576@TK2MSFTNGP15.phx.gbl...
    > I have a spreadsheet with 7 columns. In clolumns A I have some names, and

    in
    > column B some numbers, fx
    >
    > A1 1 100 200 500 800 900
    > A1 2 150 300 600 1000 1500
    > A1 3
    > A2 1
    > A2 3
    > A2 5 and so on.
    >
    > In columns C:G I have some sizes. Only some are show above.
    >
    > If I specify fx A1 in I1, 2 in I2 and 458 in I3, I need a formula that
    > returns the value 600 from row 2. That is a value that meets the condition
    > Name =A1, Number = 2 and size the smallest size, larger than the size
    > specified in I3.
    >
    > I tried with VLOOKUP in combination with IFs, and I tried with SUMPRODUCT,
    > but I have not been able to find a solution so far. Can anybody help?
    >
    > Jan
    >
    >
    >
    >




  3. #3
    Jan Kronsell
    Guest

    Re: Finding a minimum value

    Thank you Bob, it workeed perfectly OK.
    Jan

    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:OFgG0bENFHA.3704@TK2MSFTNGP12.phx.gbl...
    > Jan,
    >
    > If you can reverse the order of your sizes, that is 900,800,500,200,10
    > etc.,
    > this will work
    >
    > =INDEX(C1:G10,MATCH(I1&I2,A1:A10&B1:B10,0),MATCH(I3,INDIRECT("C"&MATCH(I1&I2
    > ,A1:A10&B1:B10,0)&":G"&MATCH(I1&I2,A1:A10&B1:B10,0)),-1))
    >
    > this is an array formula, so commit with Ctrl-Shift-Enter
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Jan Kronsell" <span@nospam.no> wrote in message
    > news:OTAxWNENFHA.576@TK2MSFTNGP15.phx.gbl...
    >> I have a spreadsheet with 7 columns. In clolumns A I have some names, and

    > in
    >> column B some numbers, fx
    >>
    >> A1 1 100 200 500 800 900
    >> A1 2 150 300 600 1000 1500
    >> A1 3
    >> A2 1
    >> A2 3
    >> A2 5 and so on.
    >>
    >> In columns C:G I have some sizes. Only some are show above.
    >>
    >> If I specify fx A1 in I1, 2 in I2 and 458 in I3, I need a formula that
    >> returns the value 600 from row 2. That is a value that meets the
    >> condition
    >> Name =A1, Number = 2 and size the smallest size, larger than the size
    >> specified in I3.
    >>
    >> I tried with VLOOKUP in combination with IFs, and I tried with
    >> SUMPRODUCT,
    >> but I have not been able to find a solution so far. Can anybody help?
    >>
    >> Jan
    >>
    >>
    >>
    >>

    >
    >




+ 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