#REF! for valid range index match

[BWMagee]

Hi,
In sheet titled '2021 Bet' I have the following formula:
{=INDEX('2021 Draw FA'!$C$51:$J$66,MATCH('2021 Bet'!$B5,'2021 Draw FA'!$B$51:$B$66,0),'2021 Bet'!$A5,'2021 Draw FA'!$C$50:$J$50)}

This formula worked perfectly. However the ranges indexed in '2021 Draw FA' contain text, and on a weekly basis I am changing the contents of J51:J66. To do this, I deleted each cell individually (by pressing 'delete' on keyboard to remove contents), then typed in the new text. After doing this, my above formula now returns #REF!
I suppose it is telling me it can no longer reference those cells, as they have been deleted. But the deletion was only temporary, they now have text again. So given the range is valid, and there is data in every cell referenced, how can I get it stop saying #REF! and return the values that are there?

Many thanks.

 

[Fluff]

You will need to re-enter the formula.

 

[RoryA]

Simply deleting the contents of the cells should not cause a #REF error.

 

[Marcelo Branco]

Missing a MATCH

I think the formula should be
=INDEX('2021 Draw FA'!$C$51:$J$66,MATCH('2021 Bet'!B5,'2021 Draw FA'!$C$51:$C$66,0),MATCH('2021 Bet'!$A5,'2021 Draw FA'!$C$50:$J$50,0))

M.

 

[BWMagee]

Missing a MATCH

I think the formula should be
=INDEX('2021 Draw FA'!$C$51:$J$66,MATCH('2021 Bet'!B5,'2021 Draw FA'!$C$51:$C$66,0),MATCH('2021 Bet'!$A5,'2021 Draw FA'!$C$50:$J$50,0))

M.

Thanks very much, it worked! Though I'm confused as to why it worked previously without the second MATCH.

 

[RoryA]

It shouldn't have. In fact, you shouldn't have even been able to enter that as a formula.