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:

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

Thank you in advance.

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.

Thank you in advance.

Last edited: