Find a specific value and returns a message


I have data in 12 different sheets. Now I want to create a cell where I put number and if the number match with any sheet then give a message "Sheet1 is containg the number" or something else. The message should be different based on which sheet contain the value. Please help me to suggest me a formula. I am new in excel.





I would use this code:

Function VLOOKUPWORKBOOK(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
'Vlookup function that will search all worksheets in the workbook - however, the data table that is being searched must be in the same
'location on every worksheet.

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets

With wSheet

Set Tble_Array = .Range(Tble_Array.Address)

vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)

End With

If Not IsEmpty(vFound) Then Exit For

Next wSheet
Set Tble_Array = Nothing

VLOOKUPWORKBOOK = "Sheet " & wSheet.Name & " contains the number."

End Function

This is a macro and is modified from our macro (UDF) here: Vlookup across the entire workbook.

To better understand the macro, read through the link. The main thing is that the data has to be in the same location on each sheet.

Also, to learn more about UDF's (user defined functions) read our tutorial on UDF's.


Answer the Question

You must create an account to use the forum. Create an Account or Login