Index(Match, 2 criterias

[Guest]

Hello,
I have tried to utilize solutions previously posted but still not getting
desired results.
I would like to populate cell e1 with value in range a1:a5 if 2 criterias
are met.
1st - value in cell d1 >= range b1:b5 and
2nd - value in cell d1 <= range c1:c5
=INDEX(A1:A5,MATCH(1,(B1:B5<=D1)*(C1:C5>=D1),0))
A B C D E
1 1 1 5 2
2 2 6 10 3
3 3 11 15 12
4 4 16 20 17
5 5 21 25 22



If any one could help, greatly appreciated.
Thanks, Steven

 

[Aladin Akyurek]

What are the desired results that must show in E?

Hello,
I have tried to utilize solutions previously posted but still no
getting
desired results.
I would like to populate cell e1 with value in range a1:a5 if
criterias
are met.
1st - value in cell d1 >= range b1:b5 and
2nd - value in cell d1 <= range c1:c5
=INDEX(A1:A5,MATCH(1,(B1:B5<=D1)*(C1:C5>=D1),0))
A B C D E
1 1 1 5 2
2 2 6 10 3
3 3 11 15 12
4 4 16 20 17
5 5 21 25 22



If any one could help, greatly appreciated.
Thanks, Steve

 

[Frank Kabel]

Hi
the formula should work. What does not work exactly?
One idea: have you entered the formula as array formula?

 

[Guest]

Hi Frank,
I have tried entering as array formula but still getting "N/A" error.
In the actual sheet
Range in column D and column E are in a separate worksheet than sourse
ranges B & C. Also all ranges are combinations of a 3 digit id # and date.
In range D - the code is combined with date order was sent out.
In range B - the code is combined with a price beginning date and
In range C - the code is combined with a price ending date.
The combinations of id # and dates are concatenated with & I have sorted the
table range B & C in so that in ascending order.

thanks

 

[Guest]

value which matches in A1:A5 range

What are the desired results that must show in E?

 

[Frank Kabel]

Hi
if you like email me a test file and describe exactyl your expected
result

 

[Guest]

Frank, I'll take you up on that.
It'll take me an hour or so to get back in position in which can.

thanks for your time.
Brgds