# Lookup value in multiple ranges

#### nanopatch

##### New Member
Hi guys,

Currently I face the following problem.
I want to match a value in a range created by a start value in the first column and an end value in the last column.
The simplified version looks like this:

 A B C 1 2 5 foo 2 10 12 bar 3 13 90 john 4 100 120 doe 5 130 132 hello 6 7 8 131

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

The value to be tested is A8.
If the value of A8 is between any of the ranges the formula should result in the C column.
So in this case 131 is between A5 and B5 and so it should yield "hello".
If A8 is 129 for instance, the returned value must be nothing (like " ").

I figured the formula had to be an array formula.
A simplified formule (in cell a10) I tried / tested looks like this:
={if(\$A1:\$A5<H131;if(index(\$A1:\$B5;match(\$A1:\$A5;\$A1:\$A5;0);2)>H1;"right";"wrong");"nope")}

this does not work and results in "nope" and an error.

In fact the final formula will work with dates having the first column start dates and the second column end dates.

Last edited:

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Excel 2010
ABC
1425foo
151012bar
161390john
17100120doe
18130132hello
19
20
21129
Sheet1
Cell Formulas
RangeFormula
C21{=IFERROR(INDEX(\$C\$14:\$C\$18,MATCH(1,(\$A\$14:\$A\$18<=\$A\$21)*(\$B\$14:\$B\$18>=\$A\$21),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

thank you for your extremely quick response. Funny enough this works, it does exactly what I need! Thank you.

But I also want to learn from this, could you please explain me what the formula does and why you use match with *?

Sure.

IFERROR(INDEX(\$C\$14:\$C\$18,MATCH(1,(\$A\$14:\$A\$18<=\$A\$21)*(\$B\$14:\$B\$18>=\$A\$21),0)),"")

INDEX(\$C\$14:\$C\$18, This essentially gives each cell in the specified range a column number and a row number. So Cell C14 would be Row 1 Column 1

Now we need to tell excel which cell in this range to return, this is where MATCH comes in.

MATCH(1, We are telling Excel to match 1 or TRUE using the following criteria

(\$A\$14:\$A\$18<=\$A\$21) Where the Column A range is less than or equal to A21

* AND

(\$B\$14:\$B\$18>=\$A\$21) Where the Column B range is greater than or equal to A21

,0) We are looking for an exact match (TRUE)

) And as our INDEX range is only 1 column wide we don't need to specify which column the result should be retrieved from.

Excel 2010
ABC
1425foo
151012bar
161390john
17100120doe
18130132hello
19
20
21131hello

</tbody>
Sheet1

So in this example 131 is greater than 130 and less than 132 so the MATCH formula would return row 5 as being the 1st set of Values that Match TRUE.

INDEX will then show you Column 1 Row 5 from your lookup range - "hello"

Apologies if this makes no sense I;m not very good at this type of thing which is why I am not a teacher :D

Thank you, I think I understand.
I had no idea that the * operator means AND.
I have tried AND() and TRUE() but this formula somehow really needs 1 and *

Replies
2
Views
130
Replies
14
Views
370
Replies
6
Views
270
Replies
16
Views
682
Replies
0
Views
105

1,203,223
Messages
6,054,228
Members
444,711
Latest member
Stupid Idiot

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back