• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

#REF Error with Index & nested Match Formula

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.



Excel Ninja
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)))


Excel Ninja
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.