MATCH formula help with Multiple worksheets

[reevesgetsaround]

Hello all

I'm hoping someone can assist with what I believe is not too difficult a task.

All I need is a formula in C2 of Sheet1 which will look across the 3 other worksheets, and where it finds the 'student' listed in B:B, and for the formula to return the name of the corresponding sheet in which they were found. Either, 'Submitted', 'In Progress' or 'Not Started'.

I have attached the sample data. My efforts kept returning TRUE or FALSE, and I could not work out how to add my own return value.

Many Thanks

[#2]

Hi @reevesgetsaround 

Thanks for providing a workbook. A Power Query option is attached:

[#3 · Accepted solution]

@reevesgetsaround 

That could be

=IF( ISNA( MATCH( Sheet1!B2, Submitted!A:A, 0) ),
 IF( ISNA( MATCH( Sheet1!B2, 'In Progress'!A:A, 0) ),
 IF( ISNA( MATCH( Sheet1!B2, 'Not yet Started'!A:A, 0) ),
     "no such",
     'Not yet Started'!$A$1),
     'In Progress'!$A$1 ),
      Submitted!$A$1 )

[#4]

@Sergei Baklan 

Thank you both. Both examples are very helpful.

[#5]

@reevesgetsaround , you are welcome