Hello
I am a basic Excel 2013 user.
I am trying to use the INDEX and MATCH function to match 2 criteria: a number and a date field together in my current worksheet to a reference set of values in a different work sheet and return a value from an adjacent cell once the combination of criteria are matched.
STS is a lookup sheet with 3 columns A$2:$C$5260
STS!A2 contains a number "ID" I wish to match with the ID number in B3 in my output worksheet.
STS!B2 contains a date field "Contact date" that I wish to match with the Contact date I3 in my output worksheet. The combination of ID and Contact date in STS is (should be) unique.
Finally STS!C2 contains a string of text that I want to display in my cell having matched the two criteria (see attached pic)
Excel problem.PNG
Here is the formula I have used in my output worksheet in the cell where I wish to return the value from the 3rd column in the STS Workbook
{=INDEX(STS!$A$2:$C$5260,MATCH(1,(STS!$A$2:$C$5260=B3)*(STS!$A$2:$C$5260=I3),0),3)}
I get the value "#NA" instead of the text from the 3rd column of STS.
When I click into the formula - the cells in my worksheet that are used as criteria are highlighted but the array STS!$A$2:$C$5260 is not highlighted with a colour as though it can't be seen.
Any ideas
Thanks in advance
Bookmarks