2

I'm working on an Excel doc and really scratching my head about a problem I have.

I have 2 worksheets, Main and Report.

The formula I'm looking for is to search the relative candidate name in the report, search for any 'complete' courses, and return the course name(s), leaving out any which are not complete.

So far, I have got closest with this formula:

=TEXTJOIN(",",TRUE,IF(Report!E:E="Complete",IF([@[Candidate Name]]=Report[Candidate Name],Report[Course Name],""),""))

However for some reason, this will return the course name from the cell BELOW the completed course. Not sure if this is due to the nested IF statement.

Any help would be much appreciated!

HOW I WANT THE OUTCOME TO LOOK

MAIN

Candidate Name Course STATUS
Alex Safeguarding, Data Protection, COMPLETE
Ben Safeguarding COMPLETE

REPORT

Candidate Name Course STATUS
Alex Safeguarding COMPLETE
Alex Data Protection COMPLETE
Ben Safeguarding COMPLETE
Ben Data Protection Not Started
MidlandJoe
  • 31
  • 3
  • The table references used in your formula don't seem to match the example table headers. Should the example headers be changed or your formula? Also you use full column reference and table reference in the same formula, which are arrays of different lengths. – P.b Aug 03 '22 at 21:02
  • I've just corrected the formula name to match the table example I gave below. -- I include the title[column header] because the data was downloaded from a folder. Not sure if this makes a difference? – MidlandJoe Aug 03 '22 at 21:04
  • And if you change `Report!E:E` into `Report[STATUS]`? – P.b Aug 03 '22 at 21:09
  • 1
    Hurrah! That's done it! Thank you so much :) – MidlandJoe Aug 03 '22 at 21:13
  • You were pretty close. `Report!E:E` range is a much longer range than `Report[STATUS]` which results in an error or not true. – P.b Aug 03 '22 at 21:18
  • You could also write it like `=TEXTJOIN(",",1,IF((Report[Candidate Name]=[@[Candidate Name]])*(Report[STATUS]=[@STATUS]),Report[Course],""))` (Ctrl+shift+enter for older Excel versions). I. Office 365 you could also use FILTER: `=TEXTJOIN(",",1,FILTER(Report[Course],(Report[Candidate Name]=[@[Candidate Name]])*(Report[STATUS]=[@STATUS]),""))` – P.b Aug 03 '22 at 21:23

0 Answers0