Using Vlookup in VBA how to get data in multiple rows with one cell value?

[Dave Smith]

Hi Experts,

I am not getting proper idea of getting values in multiple rows with single cell value using vba (vba script) also i have encountered the Error "1004" while writing my program.

I have 3 sheets having name as "Sheet1","Sheet2","Sheet3"
In the sheet 1 in column it contains various "Q1, Q2,.......................,Q15" while in the row it contains value like "Inven_01 etc".
Same as "sheet1", "sheet2" also contains same values.
Now in the "sheet3" in the Range "B5" if some enters the Inven_01 then cells adjacent to Q1 to Q15 respective value should be come (while the value for Q1, Inven_01 lies in the sheet 1)

Dim myLookupValue As String
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim myColumnIndex As Long
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myVLookupResult As Long
    
Dim myTableArray As Range
    
myLookupValue = Worksheets("Sheet3").Range("B5")
myFirstColumn = 2
myLastColumn = 3
myColumnIndex = 2
myFirstRow = 6
myLastRow = 305
    
With Worksheets("Sheet1")
Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
End With
    
myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)

Worksheets("Sheet1").Range("B9") = myVLookupResult


End Sub

If anybody help me to sort out this it will be very helpful

 

[offthelip]

I hope I have understood your requirements try this code on a copy of your workbook:

Sub test()
With Worksheets("Sheet1")
last1 = .Cells(Rows.Count, "A").End(xlUp).Row
q15arr = .Range(.Cells(1, 1), .Cells(last1, 16)) ' pick up data to column P
End With
With Worksheets("Sheet2")
last2 = .Cells(Rows.Count, "A").End(xlUp).Row
q25arr = .Range(.Cells(1, 1), .Cells(last1, 16)) ' pick up data to column P
End With
With Worksheets("Sheet3")
outarr = .Range("A9:B38") ' define output
mylookupvalue = .Range("B5")
For i = 2 To last1
 If q15arr(i, 1) = mylookupvalue Then
   For j = 2 To 16
     outarr(j - 1, 2) = q15arr(i, j)
   Next j
 End If
 If q25arr(i, 1) = mylookupvalue Then
   For j = 2 To 16
     outarr(j + 14, 2) = q25arr(i, j)
   Next j
 End If
Next i
.Range("A9:B38") = outarr
End With

End Sub

 

[DanteAmor]

With these data in your 3 sheets.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15
2	Inven_01	1050	1065	1080	1095	1110	1125	1140	1155	1170	1185	1200	1215	1230	1245	1260
3	Inven_02	4050	4065	4080	4095	4110	4125	4140	4155	4170	4185	4200	4215	4230	4245	4260
4	Inven_03	7050	7065	7080	7095	7110	7125	7140	7155	7170	7185	7200	7215	7230	7245	7260
5	Inven_04	7051	7066	7081	7096	7111	7126	7141	7156	7171	7186	7201	7216	7231	7246	7261
6	Inven_05	7052	7067	7082	7097	7112	7127	7142	7157	7172	7187	7202	7217	7232	7247	7262
7	Inven_06	7053	7068	7083	7098	7113	7128	7143	7158	7173	7188	7203	7218	7233	7248	7263
8	Inven_07	7054	7069	7084	7099	7114	7129	7144	7159	7174	7189	7204	7219	7234	7249	7264
9	Inven_08	7055	7070	7085	7100	7115	7130	7145	7160	7175	7190	7205	7220	7235	7250	7265
10	Inven_09	7056	7071	7086	7101	7116	7131	7146	7161	7176	7191	7206	7221	7236	7251	7266
11	Inven_10	7057	7072	7087	7102	7117	7132	7147	7162	7177	7192	7207	7222	7237	7252	7267
Sheet1

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Q25	Q26	Q27	Q28	Q29	Q30	Q31	Q32	Q33	Q34	Q35	Q36	Q37	Q38	Q39
2	Inven_01	5666	5586	5506	5426	5346	5266	5186	5106	5026	4946	4866	4786	4706	4626	4546
3	Inven_02	6166	6086	6006	5926	5846	5766	5686	5606	5526	5446	5366	5286	5206	5126	5046
4	Inven_03	6666	6586	6506	6426	6346	6266	6186	6106	6026	5946	5866	5786	5706	5626	5546
5	Inven_04	6667	6587	6507	6427	6347	6267	6187	6107	6027	5947	5867	5787	5707	5627	5547
6	Inven_05	6668	6588	6508	6428	6348	6268	6188	6108	6028	5948	5868	5788	5708	5628	5548
7	Inven_06	6669	6589	6509	6429	6349	6269	6189	6109	6029	5949	5869	5789	5709	5629	5549
8	Inven_07	6670	6590	6510	6430	6350	6270	6190	6110	6030	5950	5870	5790	5710	5630	5550
9	Inven_08	6671	6591	6511	6431	6351	6271	6191	6111	6031	5951	5871	5791	5711	5631	5551
10	Inven_09	6672	6592	6512	6432	6352	6272	6192	6112	6032	5952	5872	5792	5712	5632	5552
11	Inven_10	6673	6593	6513	6433	6353	6273	6193	6113	6033	5953	5873	5793	5713	5633	5553
Sheet2

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K
5	Inve_str	Inven_01	Inven_02	Inven_03	Inven_04	Inven_05	Inven_06	Inven_07	Inven_08	Inven_09	Inven_10
6
7
8
9	Q1	1050	4050	7050	7051	7052	7053	7054	7055	7056	7057
10	Q2	1065	4065	7065	7066	7067	7068	7069	7070	7071	7072
11	Q3	1080	4080	7080	7081	7082	7083	7084	7085	7086	7087
12	Q4	1095	4095	7095	7096	7097	7098	7099	7100	7101	7102
13	Q5	1110	4110	7110	7111	7112	7113	7114	7115	7116	7117
14	Q6	1125	4125	7125	7126	7127	7128	7129	7130	7131	7132
15	Q7	1140	4140	7140	7141	7142	7143	7144	7145	7146	7147
16	Q8	1155	4155	7155	7156	7157	7158	7159	7160	7161	7162
17	Q9	1170	4170	7170	7171	7172	7173	7174	7175	7176	7177
18	Q10	1185	4185	7185	7186	7187	7188	7189	7190	7191	7192
19	Q11	1200	4200	7200	7201	7202	7203	7204	7205	7206	7207
20	Q12	1215	4215	7215	7216	7217	7218	7219	7220	7221	7222
21	Q13	1230	4230	7230	7231	7232	7233	7234	7235	7236	7237
22	Q14	1245	4245	7245	7246	7247	7248	7249	7250	7251	7252
23	Q15	1260	4260	7260	7261	7262	7263	7264	7265	7266	7267
24	Q25	5666	6166	6666	6667	6668	6669	6670	6671	6672	6673
25	Q26	5586	6086	6586	6587	6588	6589	6590	6591	6592	6593
26	Q27	5506	6006	6506	6507	6508	6509	6510	6511	6512	6513
27	Q28	5426	5926	6426	6427	6428	6429	6430	6431	6432	6433
28	Q29	5346	5846	6346	6347	6348	6349	6350	6351	6352	6353
29	Q30	5266	5766	6266	6267	6268	6269	6270	6271	6272	6273
30	Q31	5186	5686	6186	6187	6188	6189	6190	6191	6192	6193
31	Q32	5106	5606	6106	6107	6108	6109	6110	6111	6112	6113
32	Q33	5026	5526	6026	6027	6028	6029	6030	6031	6032	6033
33	Q34	4946	5446	5946	5947	5948	5949	5950	5951	5952	5953
34	Q35	4866	5366	5866	5867	5868	5869	5870	5871	5872	5873
35	Q36	4786	5286	5786	5787	5788	5789	5790	5791	5792	5793
36	Q37	4706	5206	5706	5707	5708	5709	5710	5711	5712	5713
37	Q38	4626	5126	5626	5627	5628	5629	5630	5631	5632	5633
38	Q39	4546	5046	5546	5547	5548	5549	5550	5551	5552	5553
Sheet3

Try this macro:

Sub VlookupQ()
  With Sheets("Sheet3").Range("B9:K38")
    .Formula = "=IFERROR(VLOOKUP(B$5,Sheet1!$A$2:$P$11,MATCH($A9,Sheet1!$A$1:$P$1,0),0),IFERROR(VLOOKUP(B$5,Sheet2!$A$2:$P$11,MATCH($A9,Sheet2!$A$1:$P$1,0),0),""""))"
    .Value = .Value
  End With
End Sub

 

[Dave Smith]

With these data in your 3 sheets.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15
2	Inven_01	1050	1065	1080	1095	1110	1125	1140	1155	1170	1185	1200	1215	1230	1245	1260
3	Inven_02	4050	4065	4080	4095	4110	4125	4140	4155	4170	4185	4200	4215	4230	4245	4260
4	Inven_03	7050	7065	7080	7095	7110	7125	7140	7155	7170	7185	7200	7215	7230	7245	7260
5	Inven_04	7051	7066	7081	7096	7111	7126	7141	7156	7171	7186	7201	7216	7231	7246	7261
6	Inven_05	7052	7067	7082	7097	7112	7127	7142	7157	7172	7187	7202	7217	7232	7247	7262
7	Inven_06	7053	7068	7083	7098	7113	7128	7143	7158	7173	7188	7203	7218	7233	7248	7263
8	Inven_07	7054	7069	7084	7099	7114	7129	7144	7159	7174	7189	7204	7219	7234	7249	7264
9	Inven_08	7055	7070	7085	7100	7115	7130	7145	7160	7175	7190	7205	7220	7235	7250	7265
10	Inven_09	7056	7071	7086	7101	7116	7131	7146	7161	7176	7191	7206	7221	7236	7251	7266
11	Inven_10	7057	7072	7087	7102	7117	7132	7147	7162	7177	7192	7207	7222	7237	7252	7267
Sheet1

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Q25	Q26	Q27	Q28	Q29	Q30	Q31	Q32	Q33	Q34	Q35	Q36	Q37	Q38	Q39
2	Inven_01	5666	5586	5506	5426	5346	5266	5186	5106	5026	4946	4866	4786	4706	4626	4546
3	Inven_02	6166	6086	6006	5926	5846	5766	5686	5606	5526	5446	5366	5286	5206	5126	5046
4	Inven_03	6666	6586	6506	6426	6346	6266	6186	6106	6026	5946	5866	5786	5706	5626	5546
5	Inven_04	6667	6587	6507	6427	6347	6267	6187	6107	6027	5947	5867	5787	5707	5627	5547
6	Inven_05	6668	6588	6508	6428	6348	6268	6188	6108	6028	5948	5868	5788	5708	5628	5548
7	Inven_06	6669	6589	6509	6429	6349	6269	6189	6109	6029	5949	5869	5789	5709	5629	5549
8	Inven_07	6670	6590	6510	6430	6350	6270	6190	6110	6030	5950	5870	5790	5710	5630	5550
9	Inven_08	6671	6591	6511	6431	6351	6271	6191	6111	6031	5951	5871	5791	5711	5631	5551
10	Inven_09	6672	6592	6512	6432	6352	6272	6192	6112	6032	5952	5872	5792	5712	5632	5552
11	Inven_10	6673	6593	6513	6433	6353	6273	6193	6113	6033	5953	5873	5793	5713	5633	5553
Sheet2

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K
5	Inve_str	Inven_01	Inven_02	Inven_03	Inven_04	Inven_05	Inven_06	Inven_07	Inven_08	Inven_09	Inven_10
6
7
8
9	Q1	1050	4050	7050	7051	7052	7053	7054	7055	7056	7057
10	Q2	1065	4065	7065	7066	7067	7068	7069	7070	7071	7072
11	Q3	1080	4080	7080	7081	7082	7083	7084	7085	7086	7087
12	Q4	1095	4095	7095	7096	7097	7098	7099	7100	7101	7102
13	Q5	1110	4110	7110	7111	7112	7113	7114	7115	7116	7117
14	Q6	1125	4125	7125	7126	7127	7128	7129	7130	7131	7132
15	Q7	1140	4140	7140	7141	7142	7143	7144	7145	7146	7147
16	Q8	1155	4155	7155	7156	7157	7158	7159	7160	7161	7162
17	Q9	1170	4170	7170	7171	7172	7173	7174	7175	7176	7177
18	Q10	1185	4185	7185	7186	7187	7188	7189	7190	7191	7192
19	Q11	1200	4200	7200	7201	7202	7203	7204	7205	7206	7207
20	Q12	1215	4215	7215	7216	7217	7218	7219	7220	7221	7222
21	Q13	1230	4230	7230	7231	7232	7233	7234	7235	7236	7237
22	Q14	1245	4245	7245	7246	7247	7248	7249	7250	7251	7252
23	Q15	1260	4260	7260	7261	7262	7263	7264	7265	7266	7267
24	Q25	5666	6166	6666	6667	6668	6669	6670	6671	6672	6673
25	Q26	5586	6086	6586	6587	6588	6589	6590	6591	6592	6593
26	Q27	5506	6006	6506	6507	6508	6509	6510	6511	6512	6513
27	Q28	5426	5926	6426	6427	6428	6429	6430	6431	6432	6433
28	Q29	5346	5846	6346	6347	6348	6349	6350	6351	6352	6353
29	Q30	5266	5766	6266	6267	6268	6269	6270	6271	6272	6273
30	Q31	5186	5686	6186	6187	6188	6189	6190	6191	6192	6193
31	Q32	5106	5606	6106	6107	6108	6109	6110	6111	6112	6113
32	Q33	5026	5526	6026	6027	6028	6029	6030	6031	6032	6033
33	Q34	4946	5446	5946	5947	5948	5949	5950	5951	5952	5953
34	Q35	4866	5366	5866	5867	5868	5869	5870	5871	5872	5873
35	Q36	4786	5286	5786	5787	5788	5789	5790	5791	5792	5793
36	Q37	4706	5206	5706	5707	5708	5709	5710	5711	5712	5713
37	Q38	4626	5126	5626	5627	5628	5629	5630	5631	5632	5633
38	Q39	4546	5046	5546	5547	5548	5549	5550	5551	5552	5553
Sheet3

Try this macro:

Sub VlookupQ()
  With Sheets("Sheet3").Range("B9:K38")
    .Formula = "=IFERROR(VLOOKUP(B$5,Sheet1!$A$2:$P$11,MATCH($A9,Sheet1!$A$1:$P$1,0),0),IFERROR(VLOOKUP(B$5,Sheet2!$A$2:$P$11,MATCH($A9,Sheet2!$A$1:$P$1,0),0),""""))"
    .Value = .Value
  End With
End Sub

With these data in your 3 sheets.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15
2	Inven_01	1050	1065	1080	1095	1110	1125	1140	1155	1170	1185	1200	1215	1230	1245	1260
3	Inven_02	4050	4065	4080	4095	4110	4125	4140	4155	4170	4185	4200	4215	4230	4245	4260
4	Inven_03	7050	7065	7080	7095	7110	7125	7140	7155	7170	7185	7200	7215	7230	7245	7260
5	Inven_04	7051	7066	7081	7096	7111	7126	7141	7156	7171	7186	7201	7216	7231	7246	7261
6	Inven_05	7052	7067	7082	7097	7112	7127	7142	7157	7172	7187	7202	7217	7232	7247	7262
7	Inven_06	7053	7068	7083	7098	7113	7128	7143	7158	7173	7188	7203	7218	7233	7248	7263
8	Inven_07	7054	7069	7084	7099	7114	7129	7144	7159	7174	7189	7204	7219	7234	7249	7264
9	Inven_08	7055	7070	7085	7100	7115	7130	7145	7160	7175	7190	7205	7220	7235	7250	7265
10	Inven_09	7056	7071	7086	7101	7116	7131	7146	7161	7176	7191	7206	7221	7236	7251	7266
11	Inven_10	7057	7072	7087	7102	7117	7132	7147	7162	7177	7192	7207	7222	7237	7252	7267
Sheet1

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Q25	Q26	Q27	Q28	Q29	Q30	Q31	Q32	Q33	Q34	Q35	Q36	Q37	Q38	Q39
2	Inven_01	5666	5586	5506	5426	5346	5266	5186	5106	5026	4946	4866	4786	4706	4626	4546
3	Inven_02	6166	6086	6006	5926	5846	5766	5686	5606	5526	5446	5366	5286	5206	5126	5046
4	Inven_03	6666	6586	6506	6426	6346	6266	6186	6106	6026	5946	5866	5786	5706	5626	5546
5	Inven_04	6667	6587	6507	6427	6347	6267	6187	6107	6027	5947	5867	5787	5707	5627	5547
6	Inven_05	6668	6588	6508	6428	6348	6268	6188	6108	6028	5948	5868	5788	5708	5628	5548
7	Inven_06	6669	6589	6509	6429	6349	6269	6189	6109	6029	5949	5869	5789	5709	5629	5549
8	Inven_07	6670	6590	6510	6430	6350	6270	6190	6110	6030	5950	5870	5790	5710	5630	5550
9	Inven_08	6671	6591	6511	6431	6351	6271	6191	6111	6031	5951	5871	5791	5711	5631	5551
10	Inven_09	6672	6592	6512	6432	6352	6272	6192	6112	6032	5952	5872	5792	5712	5632	5552
11	Inven_10	6673	6593	6513	6433	6353	6273	6193	6113	6033	5953	5873	5793	5713	5633	5553
Sheet2

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K
5	Inve_str	Inven_01	Inven_02	Inven_03	Inven_04	Inven_05	Inven_06	Inven_07	Inven_08	Inven_09	Inven_10
6
7
8
9	Q1	1050	4050	7050	7051	7052	7053	7054	7055	7056	7057
10	Q2	1065	4065	7065	7066	7067	7068	7069	7070	7071	7072
11	Q3	1080	4080	7080	7081	7082	7083	7084	7085	7086	7087
12	Q4	1095	4095	7095	7096	7097	7098	7099	7100	7101	7102
13	Q5	1110	4110	7110	7111	7112	7113	7114	7115	7116	7117
14	Q6	1125	4125	7125	7126	7127	7128	7129	7130	7131	7132
15	Q7	1140	4140	7140	7141	7142	7143	7144	7145	7146	7147
16	Q8	1155	4155	7155	7156	7157	7158	7159	7160	7161	7162
17	Q9	1170	4170	7170	7171	7172	7173	7174	7175	7176	7177
18	Q10	1185	4185	7185	7186	7187	7188	7189	7190	7191	7192
19	Q11	1200	4200	7200	7201	7202	7203	7204	7205	7206	7207
20	Q12	1215	4215	7215	7216	7217	7218	7219	7220	7221	7222
21	Q13	1230	4230	7230	7231	7232	7233	7234	7235	7236	7237
22	Q14	1245	4245	7245	7246	7247	7248	7249	7250	7251	7252
23	Q15	1260	4260	7260	7261	7262	7263	7264	7265	7266	7267
24	Q25	5666	6166	6666	6667	6668	6669	6670	6671	6672	6673
25	Q26	5586	6086	6586	6587	6588	6589	6590	6591	6592	6593
26	Q27	5506	6006	6506	6507	6508	6509	6510	6511	6512	6513
27	Q28	5426	5926	6426	6427	6428	6429	6430	6431	6432	6433
28	Q29	5346	5846	6346	6347	6348	6349	6350	6351	6352	6353
29	Q30	5266	5766	6266	6267	6268	6269	6270	6271	6272	6273
30	Q31	5186	5686	6186	6187	6188	6189	6190	6191	6192	6193
31	Q32	5106	5606	6106	6107	6108	6109	6110	6111	6112	6113
32	Q33	5026	5526	6026	6027	6028	6029	6030	6031	6032	6033
33	Q34	4946	5446	5946	5947	5948	5949	5950	5951	5952	5953
34	Q35	4866	5366	5866	5867	5868	5869	5870	5871	5872	5873
35	Q36	4786	5286	5786	5787	5788	5789	5790	5791	5792	5793
36	Q37	4706	5206	5706	5707	5708	5709	5710	5711	5712	5713
37	Q38	4626	5126	5626	5627	5628	5629	5630	5631	5632	5633
38	Q39	4546	5046	5546	5547	5548	5549	5550	5551	5552	5553
Sheet3

Try this macro:

Sub VlookupQ()
  With Sheets("Sheet3").Range("B9:K38")
    .Formula = "=IFERROR(VLOOKUP(B$5,Sheet1!$A$2:$P$11,MATCH($A9,Sheet1!$A$1:$P$1,0),0),IFERROR(VLOOKUP(B$5,Sheet2!$A$2:$P$11,MATCH($A9,Sheet2!$A$1:$P$1,0),0),""""))"
    .Value = .Value
  End With
End Sub

Thanks a lot @DanteAmor for giving me an other idea of view of getting the

 

[Dave Smith]

Thanks a lot @DanteAmor for giving me an other idea of view of getting the

Thanks a lot @DanteAmor for giving me an other idea of view of getting the data

 

[Dave Smith]

I hope I have understood your requirements try this code on a copy of your workbook:

Sub test()
With Worksheets("Sheet1")
last1 = .Cells(Rows.Count, "A").End(xlUp).Row
q15arr = .Range(.Cells(1, 1), .Cells(last1, 16)) ' pick up data to column P
End With
With Worksheets("Sheet2")
last2 = .Cells(Rows.Count, "A").End(xlUp).Row
q25arr = .Range(.Cells(1, 1), .Cells(last1, 16)) ' pick up data to column P
End With
With Worksheets("Sheet3")
outarr = .Range("A9:B38") ' define output
mylookupvalue = .Range("B5")
For i = 2 To last1
 If q15arr(i, 1) = mylookupvalue Then
   For j = 2 To 16
     outarr(j - 1, 2) = q15arr(i, j)
   Next j
 End If
 If q25arr(i, 1) = mylookupvalue Then
   For j = 2 To 16
     outarr(j + 14, 2) = q25arr(i, j)
   Next j
 End If
Next i
.Range("A9:B38") = outarr
End With

End Sub

Thanks a lot @offthelip for sharing the vba code it worked for me