INDEX and MATCH is one row off

[matthewlouis]

I am doing INDEX and MATCH to find a date in a source table that matches and returns a percentage in the table I'm using.

The table where I am doing the INDEX and MATCH has 48.20%. So I'm indexing the date that matches that 48.20% in the source table (see below) and it says 3/7/2019. I know that is the correct date -- HOWEVER -- the return date is one day off . . . it returns 3/8/2019 which is 43.90 in the table below -- that isn't right.

Here is the formula:
IF(C7="Suspect Sideways",INDEX('Daily MTTFs'!$Q$2:$Q$101,MATCH(F7,'Daily MTTFs'!$P$2:$P$101,1)) where Column Q is the DATE and Column P is the percentage.

Here is the Source Table ('Daily MTTFs'):

<tbody>
[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]1.00[/TD]
[TD="class: xl82, width: 80"]27.80[/TD]
[TD="class: xl84, width: 80, align: right"]27.80%[/TD]
[TD="class: xl83, width: 95"]3/13/2019[/TD]

[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]2.00[/TD]
[TD="class: xl82, width: 80"]34.00[/TD]
[TD="class: xl84, align: right"]34.00%[/TD]
[TD="class: xl83"]3/12/2019[/TD]

[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]3.00[/TD]
[TD="class: xl82, width: 80"]39.10[/TD]
[TD="class: xl84, align: right"]39.10%[/TD]
[TD="class: xl83"]3/11/2019[/TD]

[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]4.00[/TD]
[TD="class: xl82, width: 80"]43.90[/TD]
[TD="class: xl84, align: right"]43.90%[/TD]
[TD="class: xl83"]3/8/2019[/TD]

[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]5.00[/TD]
[TD="class: xl82, width: 80"]48.20[/TD]
[TD="class: xl84, align: right"]48.20%[/TD]
[TD="class: xl83"]3/7/2019 [/TD]

[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]6.00[/TD]
[TD="class: xl82, width: 80"]52.30[/TD]
[TD="class: xl84, align: right"]52.30%[/TD]
[TD="class: xl83"]3/6/2019[/TD]

[TD="class: xl85"]Suspect Sideways[/TD]
[TD="class: xl82, width: 62"]7.00[/TD]
[TD="class: xl82, width: 80"]56.40[/TD]
[TD="class: xl84, align: right"]56.40%[/TD]
[TD="class: xl83"]3/5/2019[/TD]

</tbody>

Can anyone help me see why I am off one row?

Thanks in advance!!

 

[Marcelo Branco]

I suspect the value in the table is not exactly 48.20%. Maybe something like 48.20000001%

See if this array formula works
=IF(C7="Suspect Sideways",INDEX('Daily MTTFs'!$Q$2:$Q$101,MATCH(ROUND(F7,2),ROUND('Daily MTTFs'!$P$2:$P$101,2),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[matthewlouis]

Perfect!!! Thank you sir!!