find cell value in another file column with same cell value in each files

hello everyone

how can i set a formula with different files in 1 single file result?

example 1 :
formula_1.png

example 2 inside the 1 single file result
example.png

You can use this type formula :
Say, starting row is 2, then on C2

Please Login or Register  to view this content.

On D2

Please Login or Register  to view this content.

On C3

Please Login or Register  to view this content.

On D3

Please Login or Register  to view this content.

And so on. Here replace 1.xlsx and 2.xlsx with your file names.

i'm abit confuse with the code..can you explain to me?

Please upload sample files. Then I will be able to explain this clearly to you.

Hello sanram,

i am so sorry for late reply. this are the example file that i want to do with the formula.

CLASS_1.xlsm
CLASS_8.xlsm
CLASS_11.xlsm

this are the format that i've done so far. If you can see, this 3 file already been marks and there is a few name in the result sheet. i already highlight who has same name in different file.

ALL RESULT.xlsx

how can i create a formula that can detect name/participant number from each file and it automatically paste in (all result) file?
( it only detect name/participant number that has 2 or more name/participant in different file)

regard
Fish

You are trying something more critical than I thought. It will take a lot of time to figure out with formula for me. I would always like to do such a job with VBA instead of formula.