# Find value in a non-continuous range in Excel

Is there a way in Excel to look for a numeric value in a non-continuous range throughout the table?

Please refer to the image below: I need to search for the value 2 between the range minimum value and maximum value throughout the table. Here 2 lies in the range 1.93-2.286 so the displayed output will be 3rd. How is it possible?

You might be willing to try the following:

Step 1. In column H, next to each min-max-display value, create a helper column with this formula: `=IF(AND(\$B\$1>=E3,\$B\$1<=F3),1,0)`. Drag down to extend to all table. This will output 1 if the value is in each range, 0 if not.

Step 2. In column H, just down of the last cell of the table (in this example, cell H7), please insert this formula: `=IF(COUNTIF(H3:H6,1)>0,0,1)`. This will set up as 1 if the above are all 0, this is, the "not in any range" case. Place any suitable text in G7.

Step 3. In cell B2, please enter the following formula: `=LOOKUP(1,H3:H7,G3:G7)`. This will output the appropriate display value, including the "not in any range" one.

• Thanks! Now if instead of a single value I have a column of values to search in the lookup table then how to do that? – Anajz May 9 '18 at 9:59

The below works for your sample data set, enter this into Cell `B3`:

``````{=INDEX(\$G\$3:\$G\$6,MAX(IF(B1<IF(B1>\$E\$3:\$E\$6,\$F\$3:\$F\$6,0),ROWS(\$H\$3:\$H\$6),0)),1)}
``````

Adjust the ranges for your full data set and let me know how you get on.

Enter it as an array by pressing `CTRL` + `SHIFT` + `ENTER`

To make changes to suit your range, change the ROWS number. i.e `\$G\$3:\$G\$6` for rows 3 to 6 and `\$G\$3:\$G\$10` for rows 3 to 10, do this for columns E, F, G & H in the formula.

Then you can drag this down if you have values in B2, B3 etc..

• Thanks. I am sightly unclear on the formula. What values/formula to enter in cell H3:H6? And will this formula work if I have to search for multiple values in the lookup table? For eg instead of the single value in B1 if i have multiple values from cell B1:B6 to search in the lookup table then what to do? – Anajz May 9 '18 at 10:24
• @Anajz see update to answer and formula, you should be able to drag this down now as i have included \$s in the ranges. – PeterH May 9 '18 at 10:30