# Lookup values matching date range plus criteria

1. ## Lookup values matching date range plus criteria

Dear Excel-Experts,

Needing a bit of help with a date range lookup with additional criteria problem.

Sample file is attached with the expected outcome.

There are two tables.
Column A & B contain an Account number plus a start date.
In Column C, I need the corresponding Value (column I) which matches the account number (column H) and is within the date range (columns J:K) for that account.

Accounts can have multiple listings in either table.

I have highlighted a few samples. if you need any clarifications etc. just let me know.

I try to stay clear of array formula to prevent slowing down excel as the full file has a fair amount of data.

Any help/pointers are much appreciated.

2. ## Re: Lookup values matching date range plus criteria

C2
=IFERROR(INDEX(I:I,AGGREGATE(15,6,ROW(H\$1:H\$19)*(1/(--(H\$1:H\$19=A2)*--(J\$1:J\$19<=B2)*--(K\$1:K\$19>=B2))),1)),NA())

Regards.

3. ## Re: Lookup values matching date range plus criteria

Be aware that some of the dates in your lookup table are actually text!

Non-CSE option:

=LOOKUP(2,1/((\$H\$2:\$H\$19=A2)*(\$J\$2:\$J\$19<=B2)*(\$K\$2:\$K\$19>=B2)),\$I\$2:\$I\$19)

4. ## Re: Lookup values matching date range plus criteria

Thank you Khun menem,
I have configured your formula into the actual date (which is over a few sheets).
Great, fast and perfect solution. Works very well.

5. ## Re: Lookup values matching date range plus criteria

Thanks for completely ignoring my suggestion, which was posted just as fast and works just as well.

6. ## Re: Lookup values matching date range plus criteria

Originally Posted by AliGW
Thanks for completely ignoring my suggestion, which was posted just as fast and works just as well.
a few seconds later than menem, you are number two!
LOL

7. ## Re: Lookup values matching date range plus criteria

It's fine - a lack of common courtesy simply means that I am far less likely to offer help in the future. Saying "thank you" costs you nothing.

8. ## Re: Lookup values matching date range plus criteria

Thanks AliGW,
I had already fixed the date issue in my file, but thanks for pointing it out.
Your formula works perfectly too. Just need to adjust Account 22501 which should be M2 not 51. The issue here is that the start date should prevail over end date, hence this record should pull M2, so just adapting the '>=' to '>'.

9. ## Re: Lookup values matching date range plus criteria

Next time, please acknowledge ALL contributors, regardless of how good you think their responses are. We are all giving of our free time, and you must respect that.

10. ## Re: Lookup values matching date range plus criteria

Sorry AliGW,
My apologies. I received menems formula tested it and replied. Then moved on to your solution, tested it and replied. I am a bit slow to adjust the formula to the actual spreadesheet and testing, but certainly do value you formula, which works great too as well.
Thanks again to ALL who contributed.

11. ## Re: Lookup values matching date range plus criteria

Sorry again AliGW,
It was no judgment of the solution, I was just testing the first one, replied, testing the second one, replied. I certainly respect everyone's time and efforts. My apologies once more.

There are currently 1 users browsing this thread. (0 members and 1 guests)