how to use vlookup function to fetch values from multiple sheets.

[Aman Chalotra]

Hi All !!!

I have a routine work which i wish to automate with the help of VBA macro. I have a sheet with say 8 employees. I want to fetch there monthly salary from other sheet. Previously I was having only one sheet to get salary value but now I have to get it form multiple sheets. Here I want to search employee salary wrt to Employee ID from two sheets June and April. And paste in sheet1 next to employee name in two columns.

I was easy to use vlookup macro recorder but with sheets increasing its not a good Idea to use macro recorder. I have tried hard but not able to get the string to start code.

Please provide a basic code to get values.

My main sheet looks like this i.e. sheet1 :

ID	Name	June	April
D03	Jammes
D04	Mathew
D15	Anderson
D20	Hayden
D12	Stuart
D09	Broad
D30	Donald

<tbody>
</tbody>

Destination sheet One i.e. June is

ID	Salary
D03	1000
D04	2000
D05	3000
D06	4000
D07	5000
D08	6000
D09	7000
D10	8000
D11	9000
D12	10000
D13	11000
D14	12000
D15	13000
D16	14000
D17	15000
D18	16000
D19	17000
D20	18000
D21	19000
D22	20000

<tbody>
</tbody>

And second destination sheet i.e. April is:

ID	Salary
D03	100000
D04	150000
D05	200000
D06	250000
D07	300000
D08	350000
D09	400000
D10	450000
D11	500000
D12	550000
D13	600000
D14	650000
D15	700000
D16	750000
D17	800000
D18	850000
D19	900000
D20	950000
D21	1000000
D22	1050000
D23	1100000
D24	1150000
D25	1200000
D26	1250000
D27	1300000
D28	1350000

<tbody>
</tbody>

 

[hiker95]

Aman Chalotra,

Here is a macro solution for you to consider.

Sample raw data worksheets:

Excel 2007
	A	B
1	ID	Salary
2	D03	1000
3	D04	2000
4	D05	3000
5	D06	4000
6	D07	5000
7	D08	6000
8	D09	7000
9	D10	8000
10	D11	9000
11	D12	10000
12	D13	11000
13	D14	12000
14	D15	13000
15	D16	14000
16	D17	15000
17	D18	16000
18	D19	17000
19	D20	18000
20	D21	19000
21	D22	20000
22
June

Excel 2007
	A	B
1	ID	Salary
2	D03	100000
3	D04	150000
4	D05	200000
5	D06	250000
6	D07	300000
7	D08	350000
8	D09	400000
9	D10	450000
10	D11	500000
11	D12	550000
12	D13	600000
13	D14	650000
14	D15	700000
15	D16	750000
16	D17	800000
17	D18	850000
18	D19	900000
19	D20	950000
20	D21	1000000
21	D22	1050000
22	D23	1100000
23	D24	1150000
24	D25	1200000
25	D26	1250000
26	D27	1300000
27	D28	1350000
28
April

Excel 2007
	A	B	C	D	E
1	ID	Name	June	April
2	D03	Jammes
3	D04	Mathew
4	D15	Anderson
5	D20	Hayden
6	D12	Stuart
7	D09	Broad
8	D30	Donald
9
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E
1	ID	Name	June	April
2	D03	Jammes	1000	100000
3	D04	Mathew	2000	150000
4	D15	Anderson	13000	700000
5	D20	Hayden	18000	950000
6	D12	Stuart	10000	550000
7	D09	Broad	7000	400000
8	D30	Donald
9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindMonthlySalaryPerID()
' hiker95, 03/12/2017, ME995242
Dim r As Range, id As Range, lc As Long, c As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    For c = 3 To lc Step 1
      Set id = Sheets(.Cells(1, c).Value).Columns(1).Find(r.Value, LookAt:=xlWhole)
      If Not id Is Nothing Then
        .Cells(r.Row, c).Value = Sheets(.Cells(1, c).Value).Cells(id.Row, 2).Value
      End If
    Next c
  Next r
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = False
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindMonthlySalaryPerID macro.

 

[Guru008]

Hi,

Creat list with name ShtList containing name of all sheet and refer in below formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&ShtList&"'!B2:B9"),B3,INDIRECT("'"&ShtList&"'!c2:c9")))

 

[Metoo7]

=vlookup($a2,indirect(c$1&"!a:b"),2,)

 

[Aman Chalotra]

Hi hiker95.
Above code is giving Subscriopt out of range error at below
lineSet id = Sheets(.Cells(1, c).Value).Columns(1).Find(r.Value, LookAt:=xlWhole)

What could be the problem please guide.

 

[hiker95]

Hi hiker95.
Above code is giving Subscriopt out of range error at below
lineSet id = Sheets(.Cells(1, c).Value).Columns(1).Find(r.Value, LookAt:=xlWhole)

What could be the problem please guide.

Aman Chalotra,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

3. Are the screenshots in my reply #2 correct?

4. Are their formulae in worksheets June, April, and, Sheet1?


If my screenshots are correct, then, can we see your actual raw data workbook/worksheets?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com