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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>

Thank you Bob, it workeed perfectly OK.
Jan

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>
>