Hello,
I was able to formulate an Index Match statement based on 3 criteria. The 3 Criteria I needed to match from spreadsheet1 to spreadsheet2 were LastName, FirstName, and Project Code. Based on those three Criteria, I wanted to retrieve the Project code percentage from one sheet to another. (Spreadsheet2 is named QPercents and I named the ranges last, first and ProjectCode)
This following formula works fine for me:
=INDEX(QPercents!$B$2:$F$227,MATCH(R15&Q15&BR15,QPercents!last&QPercents!first&QPercents!Projectcode,0),5)
It retrieves the percentages fine.
However, if the 3 Criteria were not found, It displays #N/A
Instead of the N/A, I would like to display 100%
Any ideas? I tried using
IF(ISNA(INDEX(QPercents!$B$2:$F$227,MATCH(R15&Q15&BR15,QPercents!last&QPercents!first&QPercents!Projectcode,0),5),100%,
INDEX(QPercents!$B$2:$F$227,MATCH(R15&Q15&BR15,QPercents!last&QPercents!first&QPercents!Projectcode,0),5)
This is not working.
Thanks for any help you can give.
Bookmarks