0

I have a range say A1:E5 in Excel and now in this column has values and some are null. My question is I will give the row number dynamically to the function and for that row the function should give me the first non blank value?

         1      2      3
Row1            23
Row2                   67

When i give 2 it should return 67, when i give 1 it should give 23?

Chris
  • 5,571
  • 2
  • 20
  • 32
Ramneek Agarwal
  • 1
  • 2
  • 1
    Possible duplicate of [Find first non-blank cell in a range](http://stackoverflow.com/questions/29967410/find-first-non-blank-cell-in-a-range) – Chris Apr 22 '16 at 18:07
  • Chris, in this question i have a range and not a specific column..that is what confusing me.. row number is known and dynamic – Ramneek Agarwal Apr 22 '16 at 18:13
  • Did you try my answer below? – Scott Craner Apr 22 '16 at 18:14
  • The first answer in the linked question works equally well for rows and columns. – Chris Apr 22 '16 at 18:15
  • yes Scott..It resolves my problem,,trying to understand what exactly you have done..the function bounced of my head :) – Ramneek Agarwal Apr 22 '16 at 18:21
  • @Chris, that is one way but the fact that the OP is trying to search a range and make it more dynamic than the one that was asked, I do not see that as a viable duplicate. Also I would have done `=INDEX(B3:B100, MATCH(TRUE, INDEX(B1:B100<>"",), 0))` which does not require the Ctrl-Shift-Enter. But that's my opinion. The problem is that MATCH needs to reference a 1D array and there for it would not work on a 2D table like this. – Scott Craner Apr 22 '16 at 18:28
  • @ScottCraner Ah, okay. I completely see what you mean. I was thrown off by the formatting (or lack thereof) and thought there was only one row. – Chris Apr 22 '16 at 18:37
  • @Chris: i got the feel of index and aggregate function but can you elaborate for on column/row()*row()..how this formula is working? – Ramneek Agarwal Apr 22 '16 at 19:55

1 Answers1

0

Try this:

=INDEX($A$1:$E$5,H1,AGGREGATE(15,6,COLUMN($A$1:$E$5)/((ROW($A$1:$E$5)=H1)*($A$1:$E$5<>"")),1))

enter image description here

So the AGGREGATE() Function is Creates an array of Column numbers and Errors. When the Row number does not match H2 and/or the cell is empty it will return a divide by 0 error.

The first part of the AGGREGATE function 15 tells it we are looking for the small. The second, 6, tell it to ignore the errors, so no we have an array of only those column number that did not error. In H2's case it has an array of {2,3,4,5} from which it sends back the smallest to the Index Function.

The Index Function has three parts. The first is the Range. The second the row of that range, hence the H1. The third is the column Which we get From the Aggregate Function. In this case it would return 2 because it is the smallest.

So the Value that is at row 2 and column 2 in the range selected is 23.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81