#REF Error with Index & nested Match Formula

[Amanda McRoberts]

Good morning,

I'm working on a worksheet to pull the payment date from one table into another table, and I continue to get the #Ref error. When I run through the evaluate formula it's pulling all the necessary correct data, so I'm lost as to why I'm getting this error.

 

[Debraj]

Hi Amanda..

Welcome to the forum..

try this..
In H2..
=IF(INDEX(Payment[Paid Date],MATCH([@Invoice],Payment[REFERENCE],0))=0,"",INDEX(Payment[Paid Date],MATCH([@Invoice],Payment[REFERENCE],0)))

 

[NARAYANK991]

Hi Amanda ,

Deb has already posted the correct formula ; I'd like to understand how you say that the Evaluate Formula feature showed everything to be correct.

The formula in the uploaded workbook is :

=INDEX([@Invoice],MATCH([@Invoice],Payment[REFERENCE],0),MATCH(Invoice_Details_Tbl[[#Headers],[Paid Date]],Payment[#Headers],0))

In case you have Excel 2007 , the formula will read as :

=INDEX(Invoice_Details_Tbl[[#This Row],[Invoice]],MATCH(Invoice_Details_Tbl[[#This Row],[Invoice]],Payment[REFERENCE],0),MATCH(Invoice_Details_Tbl[[#Headers],[Paid Date]],Payment[#Headers],0))

This formula has 3 sections , which have been highlighted differently.

An INDEX formula can have 3 sections only if it is a multiple row , multiple column range , so that the second parameter will index into the rows , and the third parameter will index into the columns.

The range is specified in the first parameter ; in this case , the first parameter is a single cell , as specified by the portion highlighted in RED. There is no way this can evaluate to a valid result.

Narayan

 

[Amanda McRoberts]

Thank you for your assistance. And explaining what I did wrong.