Lowest value greater than or equal to in an array

[Adam1988]

Hi all,

Hoping you can help.

I have a 2 variable data table that has 10 rows of data and 8 columns. I have a value that I want to look up in the array and return the lowest value that is greater than or equal to the number that I want to look up. I found a way to do this within a column, but not within an array. I imagine there is a way to do with with index(match(, but I can't seem to figure it out or find an existing thread that addresses this.

I have posted a simplified example in the table below

For this example, the value that I want to look up is 100. The formula I am trying to create should return "Column 1" "Row 2" as the lowest value in the array that is greater than or equal to 100 is 105. For the results, I do not care if "Column 1" "Row 2" are returned in a single cell or in two adjacent cells.

If the lookup value is 107, the result should be "Column 2" "Row 3"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Column 1[/TD]
[TD="align: center"]Column 2[/TD]
[/TR]
[TR]
[TD="align: center"]Row 1[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]106[/TD]
[/TR]
[TR]
[TD="align: center"]Row 2[/TD]
[TD="align: center"]105[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]Row 3[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]110[/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated!

Thanks,
Adam1988

 

[XOR LX]

Hi.

It's not clear whether you want the row and column positions relative to the range in question or to the worksheet itself. For example, if your data was in D4:G6, and the closest match was in G4, would you want:

"Column 4, Row 1" (relative to range)

or:

"Column 7, Row 4" (relative to worksheet)

?

Presuming the former:

=IF(COUNTIF(Range,">="&MyValue),"Column "&1+MOD(MATCH(1,FREQUENCY(MyValue,Range),0)-1,COLUMNS(Range))&", Row "&1+INT((MATCH(1,FREQUENCY(MyValue,Range),0)-1)/COLUMNS(Range)),"No Solution")

where clearly you should replace both Range and MyValue appropriately.

Regards

 

[Adam1988]

XOR LX,

Thank you for the reply!

I'm afraid I was a little unclear with what I was asking... labeling my rows/columns as column 1,2,3 and row 1,2,3 was not the best idea.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Apples[/TD]
[TD="align: center"]Oranges[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]106[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]105[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]110[/TD]
[/TR]
</tbody>[/TABLE]

In the example, I have an array of data with column labels in cells B1:C1 (Apples, Oranges) the and row labels in cells A2:A4 (1, 2, 3).

The lookup value is in Cell D2

Lookup value = 100; Result = Apples x2
Lookup value = 107; Result = Oranges x3



Thanks again for any help!

Adam1988

 

[XOR LX]

Indeed! That's not at all like your first example!

=IF(COUNTIF(B2:C4,">="&D2),INDEX(B1:C1,1+MOD(MATCH(1,FREQUENCY(D2,B2:C4),0)-1,COLUMNS(B2:C4)))&" x"&INDEX(A2:A4,1+INT((MATCH(1,FREQUENCY(D2,B2:C4),0)-1)/COLUMNS(B2:C4))),"No Solution")

Regards

 

[Adam1988]

Yes!!! That did the trick!

Thank you for all your help!

 

[XOR LX]

You're welcome!

Cheers!