Formula for matching an ID on a sheet from 4 different sheets then returning which sheet it has a match on. And maybe renaming the returned name?

[dexbonus]

Hello,

I need help on matching alpha numerical IDs on this main sheet across 4 different sheets (used to be different workbooks but i moved them into one workbook and I've been CTRL+F-ing it and manually looking for the data) and returning which sheet it matches, the sheet is poorly named and Id like to return a more better name for it, if at all possible, (i could just rename the sheet altogether too if thats not possible)

Say for example: in Phase II sheet id like to match an ID across these sheets "reg1 may 2, 2022", "reg2 may 2, 2022" "reg3 may 2, 2022", "reg4 may 2, 2022" and return "reg4 may 2, 2022" if something matches there and maybe return it as "Region 4" If thats not possible i could just rename them to "Region 1", "Region 2" and so on.

Ive already done the Phase I sheet manually and starting the Phase II i became overwhelmed because of how tedious it is to do manually. Im not very excel savvy so excuse me if i couldnt explain it any better. If something needs clarification please let me know and ill do my best to explain it.

Ive been reading about vlookup among other things but I just cant grasp how to do it on my own. So here i am trying my luck here. Any help is appreciated!

 

[Legacy 143009]

Maybe there is a more practical way but if I were you I wold go with 4 nested IFFERROR-INDEX formula. Then wrap around an IF formula to change name. Something like:

=IF(IFERROR(INDEX(firstsheetcolumn,MATCH(searchvalue,searchcolum,0)),IFERROR(INDEX(seacondsheetcolumn,MATCH(searchvalue,searchcolum,0)),
IFERROR(INDEX(thirdsheetcolumn,MATCH(searchvalue,searchcolum,0)),IFERROR(INDEX(fouthsheetcolumn,MATCH(searchvalue,searchcolum,0)))))=searchedname,newname)

At least, this would be better than manual replace.
If I were you I would open a new column for IF condition because yuo are going to have multiple name checks. Use a helper colunm.