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]

It would be easier to understand if you made up 3 tiny tables with only 3 or 4 columns and rows and asked the question in terms of those.

I cannot understand what you are trying to do at present

 

[geigMR]

Thanks for your reply.

I have removed extraneous columns (see below). Basically, the ‘results’ columns of the formula/macro are cols J & K on sheet3 (‘hun’).

Col J looks up the matching data on sheet1 (‘arq’) and if matched returns sheet1’s col.I.

Col K looks up the matching data on sheet2 (‘jot’) and if matched returns sheet2’s col.I.

The columns to be matched (exactly) are cols B-H on the respective sheets.

(As mentioned, it appears the col.I figs on sheet2 have four spaces to right so do not have a number format, so I was hoping it woud be possible to also incorporate some kind of trim function that changes this to a number).

I hope I have explained this ok – please let me know.

sheet3:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	pro	cgt	app	pinto	fen	iso	con	res	hunFIG	arqFIG	jotFIG
2	VE4D62A2-19	0.40	F	DI1	BG	RX	BG	N	£496	not found	not found
3	VE22B85Z13-198	0.40	F	BD2	BG	RX	BG	N	£561	£552	not found
4	VE14B70H18-662	0.43	F	BBD1	G	RX	BG	F	£522	not found	not found
5	VE22B22Z20-44	0.43	F	BBD2	ID	RX	ID	N	£610	not found	not found
6	VE1A07E1-539	0.44	F	BD1	ID	ID	ID	N	£604	not found	not found
7	VE8Z33E15-5	0.45	F	DI1	BG	ID	ID	N	£502	not found	£524.80
8	VE8C53F15-431	0.45	F	DI1	ID	ID	ID	N	£514	£434	£442.80
9	VE6H81B15-341	0.47	G	BD2	G	BG	BG	M	£421	not found	not found
10	VE10Z04G11-232A	0.47	G	BBD1	RX	RX	BG	N	£602	£440	not found
11	VE14B70H18-1201	0.49	G	DI1	RX	ID	ID	N	£492	not found	not found
hun

Sheet1:

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

Sheet2:

Excel 2012
	A	B	C	D	E	F	G	H	I
1	pro	cgt	app	pinto	fen	iso	con	res	jotFIG
2	LKEK/72598/G	0.40	F	BD2	RX	BG	RX	GNT	£510.00
3	LKEK/84116/G	0.40	G	BD2	BG	RX	BG	GNT	£490.00
4	LKEK/89820/G	0.42	G	BD2	RX	RX	RX	N	£512.00
5	LKEK/02948/G	0.42	G	BD2	RX	RX	RX	N	£512.00
6	LKEK/98081/G	0.42	F	DI1	RX	BG	BG	N	£452.00
7	LKEK/52737/G	0.44	F	DI1	RX	BG	BG	N	£452.00
8	LKEK/16442/G	0.45	F	DI1	ID	ID	ID	N	£442.80
9	LKEK/44604/G	0.45	F	DI1	BG	ID	ID	N	£524.80
10	LKEK/88690/G	0.45	F	DI1	RX	RX	RX	N	£463.30
11	LKEK/89409/G	0.48	G	DI1	RX	RX	RX	N	£466.20
jot

Thanks

 

[oldbrewer]

Col J looks up the matching data on sheet1 (‘arq’) and if matched returns sheet1’s col.I.

what does looks up the matching data mean ( I know how vlookup works)

 

[geigMR]

sorry, I mean I'd like the formula/macro to compare cols B-H on both sheets, and if they match exactly then I'd like the value in col.I of sheet1 to be returned in sheet3. I have manually entered these results in col. J of sheet3 to show what the results should be.

 

[oldbrewer]

pro

cgt

app

pinto

fen

iso

con

res

arqFIG

HK72115/(-281R)

0.4

G

BBD2

BG

RX

RX

N

612

for each row in first table

HK72685/(-152R)

0.4

G

BD2

BG

RX

BG

N

606

check if there is a row

HK79895/(-239R)

0.4

F

BD2

BG

RX

BG

N

552

with columns B to H

HK85111/-239R

0.41

G

BD2

Rx

RX

RX

N

493

exactly the same

HK85332/(-239R)

0.43

F

BBD2

BG

BG

BG

N

572

HK90113/(-239R)

0.45

G

DI1

G

RX

BG

N

434

HK90115/(-219R)

0.45

F

DI1

ID

ID

ID

N

434

HK90116/(-269R)

0.46

G

DI1

BG

BG

BG

N

434

I changed the first rows to match for testing purposes

HK90202/(-298R)

0.47

G

BBD1

RX

RX

BG

N

440

HK90204/(-219R)

0.49

G

DI1

BG

RX

RX

N

434

the macro found the only 2 matching rows

not sure what you want in col A and col I

of the output table

MACRO

rownum = 39

pro

cgt

app

pinto

fen

iso

con

res

jotFIG

For a = 5 To 14

LKEK/72598/G

0.4

G

BBD2

BG

RX

RX

N

£510.00

For b = 2 To 8

LKEK/84116/G

0.4

G

BD2

BG

RX

BG

GNT

£490.00

c = a + 20

LKEK/89820/G

0.42

G

BD2

RX

RX

RX

N

£512.00

If Cells(a, b) = Cells(c, b) Then sum = sum + 1

LKEK/02948/G

0.42

G

BD2

RX

RX

RX

N

£512.00

Next b

LKEK/98081/G

0.42

F

DI1

RX

BG

BG

N

£452.00

If sum = 7 Then GoTo 200 Else GoTo 300

LKEK/52737/G

0.44

F

DI1

RX

BG

BG

N

£452.00

200 sum = 0

LKEK/16442/G

0.45

F

DI1

ID

ID

ID

N

£442.80

rownum = rownum + 1

LKEK/44604/G

0.45

F

DI1

BG

ID

ID

N

£524.80

For z = 2 To 8

LKEK/88690/G

0.45

F

DI1

RX

RX

RX

N

£463.30

Cells(rownum, z) = Cells(a, z)

LKEK/89409/G

0.48

G

DI1

RX

RX

RX

N

£466.20

Next z

300 sum = 0

Next a

End Sub

0.4

G

BBD2

BG

RX

RX

N

0.45

F

DI1

ID

ID

ID

N

<colgroup><col><col span="15"></colgroup><tbody>
</tbody>

 

[geigMR]

Hi
Unfortunately that does not seem to work for me. The only data I want to pull is the price from column I in sheet1 and sheet2. The only columns I want to be populated are column J and column K in sheet1 ('hun'), with the prices from sheet1 and sheet2 respectively - and only IF their respective columns B-H match with sheet1's. Column A on each sheet is irrelevant.

Thanks v much for any help with this!

 

[geigMR]

please note that in my example I have filled in the dummy results - in column J & K - as they should appear from the macro. In my actual spreadsheet these two columns will be empty, until the macro is run.

 

[oldbrewer]

so you just need to add a few lines to my macro to print out both prices - change for z = 2 to 8 to for z=2 to 9
then you need to add, - straight after next z, cells(rownum,z+1)=cells(c,9)

you might need to have a zz=z in the loop and use cells(rownum(zz)

 

[geigMR]

Unfortunately this is not working for me. I run the macro and nothing happens. I'm afraid I do not know much about VBA but this is the macro if I've understood your instructions correctly:

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
Cells(rownum, Z) = Cells(a, Z)
Next Z
Cells(rownum, Z + 1) = Cells(c, 9)
300 Sum = 0
Next a




End Sub

Not sure how/where to insert

a zz=z in the loop and use cells(rownum(zz)

Thanks for any answers to this.