Return value if within range, return multiple values if ranges overlap

**cde1983** · 01-18-2013, 01:30 PM

I am attempting to return a value associated with a series of ranges. I have determined what VLOOKUP formula to use if the ranges are consecutive, but I am searching for a formula that will return multiple values if the lookup value falls within two overlapping ranges and only one return value if the lookup value falls within a single non-overlapping range. My attached workbook is self-explanatory and should clear up any confusion. Thank you in advance!

**cde1983** · 01-20-2013, 11:28 AM

To clarify, I know you can use the index function to return the multiple range values, but this formula has to be copied down to multiple cells (each cell containing a single return value). I would like all possible range values to be returned in a single cell so that the data can be arranged in a single column.

**bebo021999** · 01-20-2013, 11:57 AM

Try this in F9 :

Please Login or Register  to view this content.

Confirmed with Ctrl-Shift-Enter.
Copy down

**cde1983** · 01-20-2013, 03:16 PM

Works perfectly! This saves me so much time! Thank you.

**Ujju** · 08-16-2013, 02:44 PM

I am not sure if somebody is still monitoring this page or would reply to my query but I humbly request the moderators to help me out with my issue.

The above mentioned formula just helps you out with dates overlapping in two date ranges but say in a scenario where there are multiple overlaps and still wants the output in one separated by some delimiter.

I am working on a calendar and where multiple environments overlap with each other in a date range.

Could somebody please help me out with the formula or solution at the earliest?
I'm banking on this forum for some help.

**arlu1201** · 08-17-2013, 05:53 AM

Ujju,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**Ujju** · 08-17-2013, 06:16 AM

Hi Arlu,

M sorry for violating the rules but i had already posted a new thread but in sheer desperation to get my work going i found this post and thought of replying. Please help me out with my query at http://www.excelforum.com/excel-form...20#post3368620