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

INDEX and MATCH Function HELP

A

Axim5

New Member
In the data file I have 4 columns.

1- Client Code

2- Client Name

3- Product Name

4- Date


In the data client names appear more than once, if they have bought more than one product either at the same date or at a different date.


I am trying to match the client name with the most current date after March 1, 2010.


What I have done so far, is to use INDEX and MATCH function. The syntax that I have used is as following:


INDEX(Data Table,MATCH(Client Code&Client Name&Product Name,Client Code Data&Client Name Data&Product Data,0),MATCH(Date,Date Data,-1) Control+Shift+Enter


However, the result is always comes out to be #REF error. I have checked data ranges and they are all of the same size. I have also checked if there are any errors within the Data Table and there is non.


Although I have manged to get the desired date by using VLOOKUP function but I am certain same result can be achieved through combining INDEX and MATCH function as well and it may very well be faster.


Would someone kind enough to let me know what I am doing wrong or missing here.


Thank you
 
Hi,


It sounds like there is a problem with the MATCH range.


Try breaking your formula out into each component part and see which part is causing the #REF (or use the EVALUATE FORMULA tool). Replace the element with the error with the number you would expect it to output and then confirm that the rest of the formula works. You can then just focus on a small part to fix. This technique usually works for me.


If you can't resolve it you could put the file up online (eg SKYDRIVE etc) then we can have a look.
 
You must log in or register to reply here.
Back
Top