Please I need help in vlook up using macro. I have multiple sheets in a Wk
book and would like to lookup E5 from data range in another workbook and
return column 2 data.

I want the result to be displayed in A10 across multiple sheets based on
their corresponding E5 cell lookup value.

Wk Book 1
Sheet 1 Cell E5
J45Offe_1

Sheet 2 Cell E5
Pl09Kiy_6

Wkbook 2
Cell A1 J45Offe_1 CEll B1 JungleBoy
Cell A2 Pl09Kiy_6 Cell B2 HuntingWizz

Final Output
For Sheet 1
Cell A10 = JungleBoy

For Sheet 2
Cell A10 = HuntingWizz

Check this out:
http://www.ozgrid.com/VBA/VlookupAllSheets.htm

The Custom Excel Functions

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
Col_num as Integer, Optional Range_look as Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com

'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
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
VLOOKAllSheets = vFound
End Function

To use this code do this:

1. Push Alt+F11 and go to InsertModule
2. Copy and paste in the code.
3. Push Alt+Q and Save.

Now in any cell put in the Function like this:

=VLOOKAllSheets("Dog",C1:E20,2,FALSE)

Where "Dog" is the value to find

" " C1:E20 is the range to look in the first column and find "Dog"

" " 2 is the relative column position in C1:E20 to return return our result
from.

" " FALSE (or ommited) means find and exact match of "Dog"

In other words the UDF has the exact same syntax as Excels VLOOKUP. The only
difference is that it will look in ALL Worksheets and stop at the first
match.


Regards,
Ryan---

--
RyGuy


"Yossy" wrote:

Please I need help in vlook up using macro. I have multiple sheets in a Wk
book and would like to lookup E5 from data range in another workbook and
return column 2 data.

I want the result to be displayed in A10 across multiple sheets based on
their corresponding E5 cell lookup value.

Wk Book 1
Sheet 1 Cell E5
J45Offe_1

Sheet 2 Cell E5
Pl09Kiy_6

Wkbook 2
Cell A1 J45Offe_1 CEll B1 JungleBoy
Cell A2 Pl09Kiy_6 Cell B2 HuntingWizz

Final Output
For Sheet 1
Cell A10 = JungleBoy

For Sheet 2
Cell A10 = HuntingWizz

Hi,

Your information is a little sparse, but
Sub Macro1()
'Run from Book1, cell A10, looking up value in Book1 cell E10
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[4],[Book2]Sheet1! _
R1C5:R1C6,2,FALSE)"
End Sub

Maybe this will help?
David
"Yossy" wrote:

Please I need help in vlook up using macro. I have multiple sheets in a Wk
book and would like to lookup E5 from data range in another workbook and
return column 2 data.

I want the result to be displayed in A10 across multiple sheets based on
their corresponding E5 cell lookup value.

Wk Book 1
Sheet 1 Cell E5
J45Offe_1

Sheet 2 Cell E5
Pl09Kiy_6

Wkbook 2
Cell A1 J45Offe_1 CEll B1 JungleBoy
Cell A2 Pl09Kiy_6 Cell B2 HuntingWizz

Final Output
For Sheet 1
Cell A10 = JungleBoy

For Sheet 2
Cell A10 = HuntingWizz