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

[TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 83, bgcolor: #D9D9D9"]Group[/TD]
[TD="class: xl65, width: 65, bgcolor: #D9D9D9"]Date[/TD]
[/TR]
[TR]
[TD="class: xl64"]Apples[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]10/2/18[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 242"]
<tbody>[TR]
[TD="class: xl65, width: 112, bgcolor: #D9D9D9"]Fruit (month)[/TD]
[TD="class: xl65, width: 65, bgcolor: #D9D9D9"]Start Date[/TD]
[TD="class: xl65, width: 65, bgcolor: #D9D9D9"]End Date[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Apples (Jan)[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]1/1/18[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]30/1/18[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Apples (Fab)[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]1/2/18[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]25/2/18[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Apples (Mar)[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]1/3/18[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]30/3/18[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Bananas (Feb)[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]1/2/18[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]29/2/18[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Oranges (Feb)[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]4/2/18[/TD]
[TD="class: xl68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]20/2/19[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83, bgcolor: #D9D9D9"]Fruit (month)[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[/TR]
</tbody>[/TABLE]

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!