VLookup or Offset Match for multiple criteria

[geigMR]

Hi folks

I'm not quite sure how or if this is possible but what I am wanting to do is lookup a number of column fields in another worksheet and if they ALL match exactly then return a value from that sheet. Any help would be massively appreciated! I have 3 sheets and at the far right of the 3rd sheet ('hun') I have 2 results columns: one to lookup to worksheet 1 ('arq') and the other to lookup to worksheet 2 ('jot').

Please see my attached spreadsheet. I have manually entered the results in columns U & V of 'hun' that should happen if the formula/macro works correctly. The columns in 'hun' that should be matched exactly in the other sheets are titled 'cgt', 'app', 'pinto', 'fen', 'iso', 'con', & 'res'.

sheet1:
Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	DATED:	11/12/2015
2	pro	B	cgt	app	pinto	fen	iso	con	res	J	K	L	M	N	O	arqFIG	Q
3	HK72115/(-281R)	misc	0.40	G	BBD2	BG	RX	RX	N	misc	misc	misc	misc	misc	misc	612	misc
4	HK72685/(-152R)	misc	0.40	G	BD2	BG	RX	BG	N	misc	misc	misc	misc	misc	misc	606	misc
5	HK79895/(-239R)	misc	0.40	F	BD2	BG	RX	BG	N	misc	misc	misc	misc	misc	misc	552	misc
6	HK85111/-239R	misc	0.41	G	BD2	Rx	RX	RX	N	misc	misc	misc	misc	misc	misc	493	misc
7	HK85332/(-239R)	misc	0.43	F	BBD2	BG	BG	BG	N	misc	misc	misc	misc	misc	misc	572	misc
8	HK90113/(-239R)	misc	0.45	G	DI1	G	RX	BG	N	misc	misc	misc	misc	misc	misc	434	misc
9	HK90115/(-219R)	misc	0.45	F	DI1	ID	ID	ID	N	misc	misc	misc	misc	misc	misc	434	misc
10	HK90116/(-269R)	misc	0.46	G	DI1	BG	BG	BG	N	misc	misc	misc	misc	misc	misc	434	misc
11	HK90202/(-298R)	misc	0.47	G	BBD1	RX	RX	BG	N	misc	misc	misc	misc	misc	misc	440	misc
12	HK90204/(-219R)	misc	0.49	G	DI1	BG	RX	RX	N	misc	misc	misc	misc	misc	misc	434	misc

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

arq​

Sheet2:
Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	DATED:	11/12/2015
2	A	pro	cgt	D	app	pinto	G	fen	iso	con	res	L	M	N	O	P	jotFIG	R
3	misc	LKEK/72598/G	0.40	misc	F	BD2	misc	RX	BG	RX	GNT	misc	misc	misc	misc	misc	£510.00	misc
4	misc	LKEK/84116/G	0.40	misc	G	BD2	misc	BG	RX	BG	GNT	misc	misc	misc	misc	misc	£490.00	misc
5	misc	LKEK/89820/G	0.42	misc	G	BD2	misc	RX	RX	RX	N	misc	misc	misc	misc	misc	£512.00	misc
6	misc	LKEK/02948/G	0.42	misc	G	BD2	misc	RX	RX	RX	N	misc	misc	misc	misc	misc	£512.00	misc
7	misc	LKEK/98081/G	0.42	misc	F	DI1	misc	RX	BG	BG	N	misc	misc	misc	misc	misc	£452.00	misc
8	misc	LKEK/52737/G	0.44	misc	F	DI1	misc	RX	BG	BG	N	misc	misc	misc	misc	misc	£452.00	misc
9	misc	LKEK/16442/G	0.45	misc	F	DI1	misc	ID	ID	ID	N	misc	misc	misc	misc	misc	£442.80	misc
10	misc	LKEK/44604/G	0.45	misc	F	DI1	misc	BG	ID	ID	N	misc	misc	misc	misc	misc	£524.80	misc
11	misc	LKEK/88690/G	0.45	misc	F	DI1	misc	RX	RX	RX	N	misc	misc	misc	misc	misc	£463.30	misc
12	misc	LKEK/89409/G	0.48	misc	G	DI1	misc	RX	RX	RX	N	misc	misc	misc	misc	misc	£466.20	misc

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

jot​

Sheet3:
Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	DATED:	08/12/2015																	rate	0.657255
2	pro	B	cgt	D	app	pinto	fen	iso	con	J	K	L	res	N	O	P	Q	R	S	hunFIG	arqFIG	jotFIG
3	VE4D62A2-19	misc	0.40	misc	F	DI1	BG	RX	BG	misc	misc	misc	N	misc	misc	misc	misc	$754	misc	£496	not found	not found
4	VE22B85Z13-198	misc	0.40	misc	F	BD2	BG	RX	BG	misc	misc	misc	N	misc	misc	misc	misc	$853	misc	£561	£552	not found
5	VE14B70H18-662	misc	0.43	misc	F	BBD1	G	RX	BG	misc	misc	misc	F	misc	misc	misc	misc	$794	misc	£522	not found	not found
6	VE22B22Z20-44	misc	0.43	misc	F	BBD2	ID	RX	ID	misc	misc	misc	N	misc	misc	misc	misc	$928	misc	£610	not found	not found
7	VE1A07E1-539	misc	0.44	misc	F	BD1	ID	ID	ID	misc	misc	misc	N	misc	misc	misc	misc	$918	misc	£604	not found	not found
8	VE8Z33E15-5	misc	0.45	misc	F	DI1	BG	ID	ID	misc	misc	misc	N	misc	misc	misc	misc	$764	misc	£502	not found	£524.80
9	VE8C53F15-431	misc	0.45	misc	F	DI1	ID	ID	ID	misc	misc	misc	N	misc	misc	misc	misc	$782	misc	£514	£434	£442.80
10	VE6H81B15-341	misc	0.47	misc	G	BD2	G	BG	BG	misc	misc	misc	M	misc	misc	misc	misc	$640	misc	£421	not found	not found
11	VE10Z04G11-232A	misc	0.47	misc	G	BBD1	RX	RX	BG	misc	misc	misc	N	misc	misc	misc	misc	$916	misc	£602	£440	not found
12	VE14B70H18-1201	misc	0.49	misc	G	DI1	RX	ID	ID	misc	misc	misc	N	misc	misc	misc	misc	$748	misc	£492	not found	not found

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

hun​

I have noticed that the 'jotFIG' figures (that are pasted from an external source) in col. Q of sheet 2 have four spaces to right so do not in fact have a number format, so I don't know if it is possible to also incorporate some kind of trim function that changes this to a number.

Thanks for any help!

 

[oldbrewer]

for z=2 to 9
zz=z
........................
next z
cells(rownum,zz+1)=cells(c,9)

 

[geigMR]

I now have

Sub test()'
' test Macro
'


'
rownum = 39
For a = 5 To 14
For b = 2 To 8
c = a + 20
If Cells(a, b) = Cells(c, b) Then Sum = Sum + 1
Next b
If Sum = 7 Then GoTo 200 Else GoTo 300
200 Sum = 0
rownum = rownum + 1
For Z = 2 To 9
zz = Z
Cells(rownum, Z) = Cells(a, Z)
Next Z
Cells(rownum, zz + 1) = Cells(c, 9)
300 Sum = 0
Next a


End Sub

but it still does nothing. Have I written this correctly?

 

[oldbrewer]

yes you have - ensure your data is in the same cell locations as mine

 

[oldbrewer]

eg for z=2 to 9 the 2 is because the first row of data is row 2 the 9 because there is 8 rows of data
for a = 5 to 14
this is looking at columns 5 to 14