EXCEL - ned to find a value and reference the cell above it 1

[afbutlerandassoc]

I have a row of 50 values, one has been located as the lowest of the 50. I need to find the label for the column to which it belongs. There has to be a simpler solution than 50 IF statements, but how? Thanks for your help

 

[Goska]

I am assuming that you have located the lowest value and placed this value in a seperate cell (A3).
For the location to appear, in a new cell, type

=match(A3,Range:RangeEnd,0)

This will return a value as a counter, ie A=1, B=2,.....
This only returns an exact match. This should be enough to get you in the right direction. If you need more info, the help file on MATCH is pretty decent. Or post here again and I'll come back and help some more.

 

[JGillespie]

Assuming your labels are in row 1 and your data (i only have 6 columns)is in row 2 try the following formula:

=INDIRECT(ADDRESS(1,MATCH(MIN(a2:f2),a2:f2,0)))

Min(DataRange) calculates lowest value
Match finds that value's position within your DataRange
Address(row,column) converts to an R1C1 cell reference
Indirect returns the column label you are after.

Good luck
Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[Goska]

I like JGillespie's method better. I am going to have to start using that one for myself.

 

[afbutlerandassoc]

Hello,

Thanks for the suggestions - however I can't get it to work. Here is the corrected info that I got from Excel when it tried to correct the formula:


=INDIRECT(ADDRESS(1,MATCH("min B4:AG4",B4:AG4,0)),AI4)

AI4 is the value that I want to match

Any further help would be appreciated

Thanks, Ashok

 

[Goska]

The correct code is:
=indirect(ADDRESS(1,MATCH(MIN(B4:AG4),B4:AG4,0)))

My working code for a smaller data set is:
=INDIRECT(ADDRESS(1,MATCH(MIN(A6:F6),A6:F6,0)))


In your code the A14 is not needed. The Min()function takes care of finding the smallest value of the row.

Your code with the "min B4:AG4" is telling it to search for the string "min B4:AG4", not the Min() of the row.

Your Code:
=INDIRECT(ADDRESS(1,MATCH("min B4:AG4",B4:AG4,0)),AI4)

I am assuming that your values that you are searching for the minimum of are numbers, not strings. I have a feeling that JGillespie is also thinking this, by the use of the Min() function, which does not work for strings with any consistancy.

Hope this helps.

 

[afbutlerandassoc]

Thanks Goska,

I tried this and it results in a 0 value in the cell - not the reference to the cell or the lowest value - am I doing something wrong?

Thank you, Ashok )

 

[afbutlerandassoc]

Further clarification - some of the cells in the row are blank - Is this part of my problem? Do I need to have an exclusion of the blank cells?

Thanks, )

 

[Goska]

First, you are wanting to find the minimum of an integer, correct?

Because you start in column B, there is a necessary shift that I was ignoring, since I started in A.
Try this:
indirect(ADDRESS(1,MATCH(MIN(B4:AG4),B4:AG4,0)+1))

This is also assuming that your headers are in Row1. If they are not, change the "1" in front of ,MATCH in the equation.


If you are still having problems, get me the following data to help you debug. Set some blank cells equations as the following, and tell me if they are returning the correct values or not.

=min(b4:ag4)
Should return the minimum value of the range

=MATCH(MIN(B4:AG4),B4:AG4,0)
Should return the number of the column as a reference to your data range. If you start in column B, then B will be 1, C will be 2......

=ADDRESS(1,MATCH(MIN(B4:AG4),B4:AG4,0)+1)
Should return a column and cell number in the form of $F$4.

And if all this is working, then there is a problem with the overall equation.

 

[afbutlerandassoc]

Thank you,

It seems to work - but anotehr question - is there a way to get the second and 3rd etc to 5 lowest? I have the formula that works to get the lowest value in terms of numbers: =SMALL(B4:AG4,2) - will the above work to find the names correspondent to these values as well?

I appreciate your help - this is saving me having to enter over 1000 formulas by hand.

Thanks, Ashok

 

[Goska]

Original:
=indirect(ADDRESS(1,MATCH(MIN(B4:AG4),B4:AG4,0)+1))
New:
=indirect(ADDRESS(1,MATCH(SMALL(B4:AG4,2),B4:AG4,0)+1))


Basically, swap out the MIN() and put in a SMALL()

Reuse this a few times changing the number in SMALL() to get what you want. If you want to make thigns standard for yourself, use SMALL(. : .,1) for the first.

What next?

 

[afbutlerandassoc]

It WORKED! Thanks for your help - it works like a charm and gets me out of hours of programming - you guys saved my life!

Have a great weekend!

S-)