• 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

    Hui...

  • 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.
 

Attachments

  • Nalco Invoices Billed - working.xlsx
    139.6 KB · Views: 6
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)))
 
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
 
Back
Top