|re: "My excel sheet has 4 tabs. and need to work on the first tab only, referring the next 3 tabs."|
Just to make sure we are using the correct terminology, your Excel workbook, has 4 worksheets. The sheets themselves are called worksheets and that thing with the sheet name on it is called the sheet tab.
re: "then master file with row 12345 reflects category 1"
I assume you mean the worksheet named Master. A "file" is what is stored on your hard drive by the OS.
OK, as for your question, you did not supply any details related to the layout of the worksheets, so we will have to make some assumptions.
I am assuming that all of your data is starts in A1 of each sheet. You'll have to modify this suggestion to work with your layout.
I am also assuming that each piece of data from the Master sheet is only found on a single Category sheet.
With that said, put this in Master!B1 and drag it down:
=IF(NOT(ISNA(VLOOKUP(A1,'Category 1'!A:A,1,0))),"Category 1",
IF(NOT(ISNA(VLOOKUP(A1,'Category 2'!A:A,1,0))),"Category 2",
IF(NOT(ISNA(VLOOKUP(A1,'Category 3'!A:A,1,0))),"Category 3",
"Value Not Found")))
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.