Return value from table based on 2 criteria with partial word match

[kk1352]

Hi All,


I am trying to work out a formula which allows me to return a value from a table based on 2 criteria with partial word match. I have an example below.


I would greatly appreciate any assistance!


Thanks

Group	Date
Apples	10/2/18

<tbody>
</tbody>

Fruit (month)	Start Date	End Date
Apples (Jan)	1/1/18	30/1/18
Apples (Fab)	1/2/18	25/2/18
Apples (Mar)	1/3/18	30/3/18
Bananas (Feb)	1/2/18	29/2/18
Oranges (Feb)	4/2/18	20/2/19

<tbody>
</tbody>

Fruit (month)

<tbody>
</tbody>

I would like to return a value from Fruit (month) column based on word typed in Group if date sits between that particular fruit's start and end date

 

[gsbelbin]

Not the most elegant solution but I think it does what you want. Note this is an array formula so enter with CONTROL-SHIFT-ENTER. Hopefully cell locations are obvious

=INDEX($A$5:$A$9,MATCH(A2,LEFT(IF(NOT(1-(B2>=$B$5:$B$9)*(B2<=$C$5:$C$9)),$A$5:$A$9),LEN(A2)),0))

 

[kk1352]

Not the most elegant solution but I think it does what you want. Note this is an array formula so enter with CONTROL-SHIFT-ENTER. Hopefully cell locations are obvious

=INDEX($A$5:$A$9,MATCH(A2,LEFT(IF(NOT(1-(B2>=$B$5:$B$9)*(B2<=$C$5:$C$9)),$A$5:$A$9),LEN(A2)),0))

Perfect, thank you!