Verifying data across multiple worksheet

Sadath31's excellent approach can be expanded seeing that you say that each worksheet is not the same layout.
I added numbers beside each worksheet name to be used in a VLOOKUP. This assumes that the worksheets listed are in the actual order of the worksheets or the numbering would have to correspond to the position of the worksheet in the workbook.

I amended Sadath31's ARRAY formula to the following which will identify the first worksheet that the number is found in:

Formula:

Please Login or Register  to view this content.

I also amended the worksheet names with cell ranges to include all the columns of a worksheet. This is overkill and if you know the limits of the data then the ranges can be modified.
Eg. Sheet2!a:xfd

Results:

	A	B	C	D	E	F	G	H	I
1	Tel No								Sheets Names
2	52156	Sheet2!a:xfd						1	Sheet2!a:xfd
3	51353	SHeet3!a:xfd						2	SHeet3!a:xfd
4	64652	Not Found						3	SHeet4!a:xfd
5	6546	Sheet2!a:xfd

=IFERROR(VLOOKUP(MATCH(TRUE,COUNTIF(INDIRECT(ShName),A2)>0,0),$H$2:$I$4,2,0),"Not Found")
Starting from the centre of the formula.
ShName is a definded name for the contents of the range I2:I4
INDIRECT makes the defined name into an actual useable reference to another worksheet
COUNTIF counts the occurrences of the value in A2 in the list of worksheets and their cell ranges and is looking for a value greater than 0
The COUNTIF and INDIRECT are inside the MATCH function. MATCH takes 3 arguments. First the value which is A2, second is the array which in this case is defined by the INDIRECT function and lastly the 0 means an EXACT match must be found.
The MATCH will return a value in this case from 1 to 3 if the value in A2 is found or an error if it is not found.
VLOOKUP will lookup the value that is found in the range $H$2:$i$4 and return the value in the second column (the 2) that matches the value that MATCH found. The 0 means an EXACT match must be found in the first column.
IFERROR if the VLOOKUP results in an error, the value at the end of the formula "Not Found" will be returned.

All of this is based on the formula that Sadath31 first gave to you. It is just expanded a bit.