Formula to find the Non blank value between the ranges

[jebam3001]

Dear Experts,
I am struggling with an excel formula to find the exact non blank value for certain criteria.
Kindly review the attachment.
Yellow cells are the inputs which will change dynamically.
Green cells are the expected values I need from the formula.
From cell E4 the raw data starts,from E3 the header starts.
Here is the scenario.
Cell A1 has value as 30.I want to extract the non blank value which appears from E4 based on the 3rd row headers.For Input 30, I want the first non blank value which appears immediately after value 30 by comparing the headers.In this I have the first non blank value at P4 for the header 46 which is after 30.

After that I need another formula which has the same value but goes rever

[#2]

@jebam3001 

I suspect your challenge is crystal clear in your own mind. It isn't as clear to the reader (at least not this one).

• It's not clear what the relationships are between A1 and the various headers in row 4 (i.e., E4 to W4) and how those figure into your description above
• It's not clear what the numbers in green are, and how they relate to the rest.
• The number "60" in cell C1 was entered as a text, so wouldn't have worked unless you were translating it with the VALUE function elsewhere in the sheet.
• And so forth.

That may be part of why you're struggling with the formula. You need to be able to articulate -- like spell out in step form what you mean by your statement:

For Input 30, I want the first non blank value which appears immediately after value 30 by comparing the headers.In this I have the first non blank value at P4 for the header 46 which is after 30.

How did you get from the value 30 to the value 137.71? Walk us through the steps (assuming that's the "right answer"--what makes it "right"?)  What produces the "blank values" and why are they blank?

With a more complete description, we might be able to come up with a formula or procedure, a series of formulas in each of the cells....

[#3]

@mathetes 

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

[#4]

@jebam3001 

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.

[#5 · Accepted solution]

@jebam3001 

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)

[#6]

@Riny_van_Eekelen 

Thanks for the help.

[#7]

@Sergei Baklan 

Its working as expected.

BIG Thanks for the nice solution

Regards

Jeba

[#8]

@Sergei Baklan 

Must say that your solution is much more elegant than mine and that I'm still learning new things every time. For instance, I had no idea that "--C1" could make the text " '60 " into a number.

Your formulae give #NA and/or #NUM when you look up values for numbers that have no value "before" or "after" where mine produce -zero-. And I guess that this is no problem, as one can always ignore these in calculations (if necessary) by using AGGREGATE. But I keep forgetting this, when I strive to avoid of any kind of error. But, after testing my own formulae a bit more careful now, I notice that they produce #NA if the number looked up is greater than 77. So, I failed :(

Lastly, I took the requirement "before" and "after" literally. "Equal to" is neither "before" or "after". Therefore I raised the question towards the end of my original post.

[#9]

@Riny_van_Eekelen 

Thank you for the feedback.

If error handling is the case I'd prefer to wrap formulas with IFERROR and return some text which indicates the error. That's better to separate blank values and zeroes.

And you are absolutely right with "equal to" case. If to exclude such cases we may change ($E$3:$W$3>=--A$1) on ($E$3:$W$3>--A$1) for AGGREGATE and add one more criteria

($E$3:$W$3<>--A$1)

for the LOOKUP().

Like in attached.

[#10]

Hi @Sergei Baklan ,

I didn't originate this conversation, so I don't know what's required with required to the "equal to" question. Thats up to  @jebam3001 to determine, as for the error handling. I merely raised the questions since I struggled with it when I worked on my version of the solution. Happy Holidays!