Perhaps this UDF will help:

Function mvlookup(srchval, srchindex)
Dim sh As Worksheet
Dim srchrng As Range
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<== change
Set srchrng = sh.Range("A:B") '<=== change range
res = Application.VLookup(srchval, srchrng, srchindex, 0)
If Not IsError(res) Then
mvlookup = res
Exit Function
End If
Next sh
mvlookup = ""
End Function

e.g in a cell put:

=MVLOOKUP("abc",2)

will find "abc" and return value from column B

"Chrisl147" wrote:

I regularily use VLOOKUP but want to create a LOOKUP that will search all
pages of a workbook and return the required data. I am using a distinct
customer number that will only appear once.