Vlookup or Index Match to check value across multiple sheets

Hi..
I am working on a file which has got 5 spreadsheets viz Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5.. Every sheet has got a column called Tag which consists of a integer value. Sheet 4 consists of tag and owner. Now I want to populate the value in sheet 5 only if the tag in Sheet 4 matches with the tag in any of the sheets(sheet 1, sheet 2,sheet 3). If the tag is not available in any of the sheet. It should return Blank.

I applied And with Index match but it is giving me #Value Error.

Please help.

Thanks in advance for the help

I attached a sample sheet with UDF(User Defined Function)

Originally Posted by Ali Kırksekiz

I attached a sample sheet with UDF(User Defined Function)

Hi Ali..
I appreciate your reply..but I am still getting #Name errorAli.JPG

when I opened the workbook, it prompted that there is circular reference.. I am not sure whether I have made my question clear or not. I have got (tags & owners) in sheet 4. and only tags in (sheet 1, sheet 2, sheet 3)... Sheet 5 and sheet 4 are of the same format.. I want that sheet 5 should populate the owner name if the tag is present in (sheet 4) & in any of the sheets (sheet 1, sheet 2, sheet 3). If the value in sheet 4 could not match in (sheet 1 or sheet 2 or sheet 3) then the Owner in Sheet 5 should return Blank.

What i could get from your UDF was that you have taken every worksheet in consideration to match

I hope i am not confusing you..Please let me know if you have any doubt.

Alternative formula

Please Login or Register  to view this content.