0

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:

enter image description here

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?

Thanks in advance.

1

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.

| improve this answer | |
  • 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
1

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..

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.