JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
Table name: Currency, sheet name Currency
I have the following index formula on another sheet:
=INDEX(Currency,MATCH(D2,Currency[#Headers],0),MATCH(B2,Currency[[#Alll],[Date]],0))
D2 is a date, B2 is a currency label, both exist in the table
If I use Evaluate Formula, it becomes:
=INDEX('Currency'!$A$2,$R$7710,2,76)
Which returns #REF, despite valid values for row and column index numbers (i.e. Match returns a number)
Can anyone tell why? The cell it finds is B76 (which is within range A2:R7710) and contains value 1, so why does the formula return #REF!?
TIA,
Jack
Table name: Currency, sheet name Currency
I have the following index formula on another sheet:
=INDEX(Currency,MATCH(D2,Currency[#Headers],0),MATCH(B2,Currency[[#Alll],[Date]],0))
D2 is a date, B2 is a currency label, both exist in the table
If I use Evaluate Formula, it becomes:
=INDEX('Currency'!$A$2,$R$7710,2,76)
Which returns #REF, despite valid values for row and column index numbers (i.e. Match returns a number)
Can anyone tell why? The cell it finds is B76 (which is within range A2:R7710) and contains value 1, so why does the formula return #REF!?
TIA,
Jack