Lookup value in multiple ranges

[nanopatch]

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:


[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl66, width: 64"]5[/TD]
[TD="class: xl66, width: 64"]foo[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66, width: 64"]10[/TD]
[TD="class: xl66, width: 64"]12[/TD]
[TD="class: xl66, width: 64"]bar[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl66, width: 64"]13[/TD]
[TD="class: xl66, width: 64"]90[/TD]
[TD="class: xl66, width: 64"]john[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl66, width: 64"]100[/TD]
[TD="class: xl66, width: 64"]120[/TD]
[TD="class: xl66, width: 64"]doe[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl66, width: 64"]130[/TD]
[TD="class: xl66, width: 64"]132[/TD]
[TD="class: xl66, width: 64"]hello[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl67, width: 64"]131[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


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.

Thank you in advance.

 

[Comfy]

Excel 2010
	A	B	C
14	2	5	foo
15	10	12	bar
16	13	90	john
17	100	120	doe
18	130	132	hello
19
20
21	129
Sheet1

Cell Formulas
Range	Formula
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.

 

[nanopatch]

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 *?

 

[Comfy]

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

	A	B	C
foo
bar
john
doe
hello
hello

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]90[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]120[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]132[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"][/TD]

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

 

[nanopatch]

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 *