Find solution
How to find the column name of the first column with a null value?

What formula can I use to find the lowest value in a range of cells (in a row) and return the column header?

  • I have a spreadsheet that has 20+ columns. In 7 of the columns, I have a dollar value ($0.01, $1.00, etc. or NULL if there's no value). I'm trying to find a formula that will look at a row, pick the lowest value in that row from one of the 7 columns, and then tell me which column it came from (ie return the column header). I'm sure there's some sort of nested formula I need to do. Anyone have any ideas?? Please!! I'm dying over here!

  • Answer:

    you can try the index() & match() on a test grouped table - your header rows are in B1 to H1 - your row you want to lookup is in B17 to H17 - then in M17 or A17 or whatever copy&paste this formula =INDEX($B$1:H17,1, MATCH(MIN(B17:H17), B17:H17,0)) then fill the formula up and down the column that will show the header of the minimum row. it will ignore the blank "Null", it will give an #N/A if the whole row is null a tie in the minimum means the number on the left will win. that works for grouped data, but you say your data is interspersed? what are the 7 column? and are the 7 columns always going to be the same 7? will there be numbers in the columns you want to ignore (if its just text that you want to ignore, then the formula above will work ok)? the easiest way to deal with interspersion is to use the macro that Garbo can write...plus you wouldnt have to go through the step of consolidating your data.

Somewhat of a Mystery at Yahoo! Answers Visit the source

Was this solution helpful to you?

Other answers

Expletiv has a very elegant Excel solution that is the best answer for grouped data. The following macro will do essentially the same thing. If your data is not in contiguous rows, please advise the specific columns in play and I will modify it. Sub LowBidder() Dim rng As Range Dim i As Long Dim LastRow As Long If WorksheetFunction.CountA(Cells) > 0 Then LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End If Set rng = Range("B2:B" & LastRow) For Each cell In rng cell.Select For i = 2 To 6 If Cells(ActiveCell.Row, i).Value = _ Application.Min(Range("B" & _ ActiveCell.Row & ":" & "H" & ActiveCell.Row)) Then Range("A" & ActiveCell.Row).Value = _ Range(Left(Cells (ActiveCell.Row, i). Address(0, 0), 1) & 1) End If Next i Next End Sub

Try this in your header: High And Low Values You can use Excel's Circular Reference tool to have a cell that contains the highest ever reached value. For example, suppose you have a worksheet used to track team scores. You can set up a cell that will contain the highest score ever reached, even if that score is deleted from the list. Suppose the score are in A1:A10. First, go to the Tools->Options dialog, click on the Calculation tab, and check the Interations check box. Then, enter the following formula in cell B1: =MAX(A1:A10,B1) Cell B1 will contain the highest value that has ever been present in A1:A10, even if that value is deleted from the range. Use the =MIN function to get the lowest ever value.

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.