Need a way of retuning a fixed value from an adjacent calculated value column

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I've got a column with calculated values and an adjacent
column that has a fixed value. The calculated column values
will vary according to the input and they are not sorted.
I need a way of finding the smallest value in the calculated
column and subsequently returning the adjacent fixed
column value.

Any help would be greatly appreciated.

Thanks,

Jim
 
One way, assuming calc. values in A1:A10, fixed values in
B1:B10:

=OFFSET(A1,MATCH(MIN(A1:A10),A1:A10,0)-1,1)

HTH
Jason
Atlanta, GA
 
Hi.

Your calculated values in A1:A5, fixed values in B1:B5.
=INDEX(B1:B5,MATCH(MIN(A1:A5),A1:A5,0))

Alain CROS.
 
Jim

One way to do it:

Column G holds the calculated values.
In case of multiple instances of the minimum
value, the value closest to the top of the
column is chosen.

=INDEX(H6:H15,MIN(IF(G6:G15=MIN(G6:G15),ROW(G6:G15)-ROW(G6)+1)))

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter> instead of <Enter>, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in curly brackets { } Don't enter these brackets yourself.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.
 
Back
Top