Finding a minimum value

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

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)
 
Thank you Bob, it workeed perfectly OK.
Jan

Bob Phillips said:
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)
Click to expand...
 
Back
Top