# Finding a minimum value

1. ## 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. ## 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. ## 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
>>
>>
>>
>>

>
>

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

#### 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