Look up range of numbers

[gheyman]

As you will see from the below formula (included as a reference) I have a bunch of cells that represent ranges. Example 1q4 equates to range 1 to 4. I need a formula that will be give me the corresponding range. So if I entered "10" in cell U12 the formula would result in "10q24" Or if I entered "106" the formula would result in 100q199

I have been trying to manipulate the lookup part of the formula below but not having any luck

=+COUNTIFS(INDEX(Source_Data!$A$5:$AQ$90000,,MATCH(IF($W$5="","",(LOOKUP($W$5,{0,0;1,"1q4";5,"5q9";10,"10q24";25,"25q49";50,"50q74";75,"75q99";100,"100q199";200,"200q249";250,"250q499";500,"500q749";750,"750q999";1000,"1000q1999";2000,"2000q2999";3000,"3000q4999";5000,"5000q5999";6000,"6000q7499";7500,"7500q9999";10000,"10000q12999";13000,"13000q14999";15000,"15000q19999";20000,"20000q22999";23000,"23000q25999";26000,"26000q29999"}))),Source_Data!$A$5:$AQ$5,0)),">0",INDEX(Source_Data!$A$5:$AQ$90000,,MATCH(IF($W$6="","",(LOOKUP($W$6,{0,0;1,"1q4";5,"5q9";10,"10q24";25,"25q49";50,"50q74";75,"75q99";100,"100q199";200,"200q249";250,"250q499";500,"500q749";750,"750q999";1000,"1000q1999";2000,"2000q2999";3000,"3000q4999";5000,"5000q5999";6000,"6000q7499";7500,"7500q9999";10000,"10000q12999";13000,"13000q14999";15000,"15000q19999";20000,"20000q22999";23000,"23000q25999";26000,"26000q29999"}))),Source_Data!$A$5:$AQ$5,0)),">0",INDEX(Source_Data!$A$5:$AQ$90000,,1),$W$4)

 

[Fluff]

That's what your lookup is doing

 

[Eric W]

I think you need to change your LOOKUP function to a VLOOKUP function, since you're giving it a 2-D array instead of 2 1-D arrays. I'd also recommend taking the arrays out of the formula altogether and making a table on a sheet to hold the data. It would make the formula much easier to read, and easier to change the table if need be.

 

[gheyman]

I just included the Countif formula to help explain. So that you can see the "Range" titles.... That formula also shows the where the data is that I am searching ( Source_Data!$A$5:$AQ$5).

 

[Fluff]

You originally said

I need a formula that will be give me the corresponding range. So if I entered "10" in cell U12 the formula would result in "10q24" Or if I entered "106" the formula would result in 100q199

Which is exactly what your lookup function does

LOOKUP($W$5,{0,0;1,"1q4";5,"5q9";10,"10q24";25,"25q49";50,"50q74";75,"75q99";100,"100q199";200,"200q249";250,"250q499";500,"500q749";750,"750q999";1000,"1000q1999";2000,"2000q2999";3000,"3000q4999";5000,"5000q5999";6000,"6000q7499";7500,"7500q9999";10000,"10000q12999";13000,"13000q14999";15000,"15000q19999";20000,"20000q22999";23000,"23000q25999";26000,"26000q29999"})

(for me at least) so not sure what you are asking for.

 

[gheyman]

Thank you!!

 

[Fluff]

You're welcome.