# vlookup over multiple sheets (same workbook) help pelase

#### chally3

##### Board Regular
Can anyone help with a formula please, I am really stuck on this one.
I am trying to write a vlookup over multiple sheets but cannot get it to work.

When I download the new spreadsheet I need the vlookup to look across the worksheets to see whom has this items..

Sheet 1 columns

Column A Column B
Item Qty reqd

Sheets 2,3,4 & 5 are named as the buyers initials below

Sheet 2 = MC
Sheet 3 = ELU
Sheet 4 = TJA
Sheet 5 = TH

All of these sheets have the same columns as below

Column A Column B Column C
Item Qty reqd Initials

What I would like the formula to do is look in sheet 1 at the ‘item’ (say cell a1), then check the other worksheets & copy across whose initials (from column C,) that relate to that item.

Can this be done,

Thank you

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
see my notes in my hand book -- venkat

But VLOOKUP is only using one lookup value? You can use this technique

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A1:A50"),A1)>0,0))&"'!A:B"),2,0)

Where a list of sheet names is named "MySheets" and it will allow a de facto
VLOOKUP over multiple sheets.
The latter formula needs to be entered with ctrl + shift & enter.

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

the above is reply to the following

"EXCELLRNR" <EXCELLRNR@discussions.microsoft.com> wrote in message
> How can I create a lookup formula that looks at information in cell V9 and
> S9
> in sheet 1 and finds the information in one of 5 other sheets to return
> the
> correct value in cell K9 of sheet 1?

Hi,

If you prefer a VBA approach..

Code:
``````Sub test()
Dim i       As Byte
Dim lRow    As Long
Dim Rng     As Range
Dim ItemRng As Range
Dim fRow    As Long
Dim Itm     As Range
Dim ws      As Worksheet

Set ws = Sheets("Sheet1")
fRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set ItemRng = ws.Range("A2:A" & fRow)
Application.ScreenUpdating = False
For Each Itm In ItemRng
For i = 1 To Sheets.Count
If Sheets(i).Name <> ws.Name Then
Sheets(i).Activate
lRow = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:C" & lRow)
If Application.WorksheetFunction.CountIf(Columns(1), Itm) > 0 Then
With Rng
.AutoFilter field:=1, Criteria1:=Itm
.Offset(1, 2).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
ws.Cells(Itm.Row, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValues, , , True
.AutoFilter
End With
End If
End If
Next
Next
ws.Activate
Application.ScreenUpdating = True
End Sub``````

How to run:

1. Hit Alt+F11
2. Go to Insert > Module
3. Paste the code there
4. Close the window
5. Go to Tools > Macros > Macro and run the test

HTH

Replies
1
Views
472
Replies
8
Views
315
Replies
4
Views
200
Replies
1
Views
225
Replies
5
Views
1K

1,207,438
Messages
6,078,561
Members
446,349
Latest member
Malroos7912

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back