Excel find closest bigger value in a column to a given lookup value

[jplou]

I am going to input data in A2:A100. Based on a formula, a calculation is made and returns results in J2:J100. The lookup value is in cell P2. There are cells in column J that are currently displaying "#DIV/0!", due to the fact that there are cells in column A that have not been filled yet.
I am trying to find the closest bigger value in A2:A100, using as lookup value P2. I input in Q2 the following array formula (ctrl+shift+enter): =LARGE(J2:J100;COUNTIF(J2:J100;">"&$P$2)) This formula returns "#DIV/0!". I assume that cells in column J that are currently displaying "#DIV/0!" are the cause of the problem. It is kind of important not to have false zeroes in column J, so i cannot use the IFERROR function.
Could anyone please help me to get excel to ignore these cells in column J and find the closest bigger value in regards to the lookup value in P2? Thank you in advance!

 

[admiral100]

Hi,

Hope I understood you correctly ....try this:-

=INDEX(A2:A100,MATCH($P$2,A2:A100,1))

Or if you looking for column J

=INDEX(J2:J100,MATCH($P$2,J2:J100,1))

 

[jplou]

I found out that the following formula does the trick: =AGGREGATE(14,6,J2:J100,COUNTIF(J2:J100,">"&$P$2))<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Thank you very much for taking the time to respond!</code>

 

[admiral100]

NP , Glad you got along

 

[Aladin Akyurek]

I found out that the following formula does the trick: =AGGREGATE(14,6,J2:J100,COUNTIF(J2:J100,">"&$P$2))<code style='margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;'>Thank you very much for taking the time to respond!</code>

In the original post, you have been talking about A2:A100. In this formula there is no trace of that range. How come? Care perhaps to post a sample of 5 rows along with the expected result?

 

[barry houdini]

Cross-posted here