#REF! for valid range index match

BWMagee

Board Regular
Joined
Feb 18, 2014
Messages
105
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,511
Office Version
  1. 365
Platform
  1. Windows
You will need to re-enter the formula.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Simply deleting the contents of the cells should not cause a #REF error.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,519
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Solution

BWMagee

Board Regular
Joined
Feb 18, 2014
Messages
105
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It shouldn't have. In fact, you shouldn't have even been able to enter that as a formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,447
Messages
5,636,326
Members
416,912
Latest member
danluk12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top