Forum Discussion
Formula to find the Non blank value between the ranges
As variant with non-array formulas
First after
=INDEX($E4:$W4,1,AGGREGATE(15,6,1/($E4:$W4<>"")/($E$3:$W$3>=--A$1)*(COLUMN($E$3:$W$3)-COLUMN($E$1)+1),1),1)Immed before
=LOOKUP(--A$1,1/($E4:$W4<>"")*$E$3:$W$3,$E4:$W4)
Thanks for your reply and apologies for the inconvenience caused.
I will give more details which might help you to understand the problem better.
The requirement is I want to extract the value from cells E4 to W6 based on the Inputs I use in cells A1 and C1.
In my example it was 30 and 60 and I am going to explain how I need the answers when I input 30.
Here is how I extract the value 137.71 in cell A4.I need the first non blank value which appears after(Greater than) my input value.The headers are in cell E3 to W3.My input value is 30 and 44 is greater than 30 which is in N3 but this has blank data in N3.Hence I want to extract the first non blank value which appears where the header is greater than my input. 44(N3/N4) and 45(O3/O4) has blanks.Hence the value which is non blank available in P4(137.71) and the header is 46 which is also greater then 30 which is my input.Hence that is what I need as output in A4.Now the same logic should go reverse to find the non blank value appears before 30.here the cell J4 has 115.61 and the header is 18 which is less than 30.
Like the same above I need the other rows to be filled dynamically. In the row 5 I had to extract from O5(Header 45 which is greater than 30 and the first non blank value appears after 30) etc..
Kindly let me know if I am still confusing
Regards
Jeba
Hopefully, the attached workbook contains a solution to your problem. But, it wasn't at all straight forward. By using several helper columns and rows, with multiple formulae, I could find the values you want. This avoided creating a monster formula that would have been impossible to understand or maintain. I've hidden these helper columns and rows "behind" the group-buttons. You can view them by pressing the plus-signs and hide them again by pressing the minus-signs.
One matter I wondered is which values you expect to see when your chosen number (table on the left) is equal to one that is in the header of the table on the right. E.g. 46 on the first row. This will now produce 137.71 (being equal to or after) and 115.61 (before). If you want it to be 124.46 (after) and 137.71 (equal to or before) you need to play with the numbers in the header. In the latter case. You could type 45.9 and round it to display 46 (and do this for all your table headers). However, if you want to see 124.46 (after) and 115.61 (before), thus excluding the value 137.71 (equal to), several formulae need to be amended.