Index Match Match - 2 x Vertical Lookups - Not working as it should

Hi there,

I have a dataset where I have numerous contractors working on a variety of projects, some of the same contractors are working on more than one contract.

So my dataset shows:

Contractor / Project in 2 separate columns

Differing projects are on differing Cost Centres (5 to be precise), so I want to be able to run a check from my dataset back to the summary to ensure the data entered is onto the correct Cost centre.

I have used Index/Match/Match as opposed to doing a concatenate of the supplier/project as the prior will involve a new column in each the data and the summary or a long syntax.

My formula:

PHP Code: 

INDEX('ALL CONSULTANCY'!$A:$A,MATCH('52112001_Data'!$AD210,'ALL CONSULTANCY'!$C:$C,MATCH('52112001_Data'!$AE210,'ALL CONSULTANCY'!$D:$D,0))) 


Breakdown being: Where my Cost centre list is in my summary $A:A, Supplier Name in Data, Supplier name in Summary, Project name in Data, Project name in Summary.

The above formula is returning an answer but it is wrong, but I'm not sure why.

I have tried to alter it a few ways but then I get REF?

Thanks
seash

Try ...

=INDEX('ALL CONSULTANCY'!$A:$A,MATCH('52112001_Data'!$AD210,'ALL CONSULTANCY'!$C:$C,0),MATCH('52112001_Data'!$AE210,'ALL CONSULTANCY'!$D:$D,0))

Hi John,

I tried that already, returns me REF?

Thanks
seash

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.