Index / match - multiple values

[dunmore83]

Hi,

The following table is in worksheet 'Calvings-By-Cow'

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]G[/TD]
[TD]M[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]23-May-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]29-Sep-14[/TD]
[TD]09-Sep-15[/TD]
[TD][/TD]
[TD]26-Aug-16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]18[/TD]
[TD]12-Jan-15[/TD]
[TD]23-Sep-16[/TD]
[TD][/TD]
[TD]21-Sep-17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22[/TD]
[TD]05-Apr-15[/TD]
[TD]09-Nov-15[/TD]
[TD][/TD]
[TD]02-Nov-16[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









The following table is the worksheet I need a formula to go in column G

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]30-May-14[/TD]
[TD]23-May-14[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]30-Dec-15[/TD]
[TD]09-Sep-15[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]30-Sep-17[/TD]
[TD]21-Sep-17[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]15-Dec-15[/TD]
[TD]09-Nov-15[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









The formula in column G needs to 1st match the value in column M with the value in Column G in 'Calvings-By-Cow' & then it needs to return the date from column M in 'Calvings-By-Cow' if only 1 date is found in that row. If more than 2 dates are found in that row then it needs to return the value that is the 'closest less than date' in the row match in 'Calvings-By-Cow' to column D.

I have inserted the dates in column G that the formula needs to return.

Any helpers with this formula ??

 

[steve the fish]

This is going to be very difficult and maybe not possible with native formulas if we cant use contiguous range refences to do the tests. Your tables seem split up the ranges you need to test.

 

[dunmore83]

Thanks.

Would the formula be easier if the dates in 'Calvings-By-Cow' were all in one single column ? (rather than on separate rows)