Vlookup and match issue

[petrutms]

Hi, i am trying to find a value in one sheet and match in on another. I used vlookup but I cant find the right syntax. Index works but if I drag the formula doesn't. Attached a sample to understand what I need: I need to find "a" and "b " values from values sheet and paste them in county sheet, with drag formula!
Any help is appreciated !

Book1
	A	B	C	D	E	F	G	H	I
1		jan	feb	mar	apr	may	jun	jul	aug
2	a
3	b
county

Book1
	A	B	C	D	E	F	G	H	I
1		jan	feb	mar	apr	may	jun	jul	aug
2		1	1	1	1	1	1	1	1
3	s	4	4	4	4	4	4	4	4
4	c	2	2	2	2	2	2	2	2
5	d	4	4	4	4	4	4	4	4
6	e	5	5	5	5	5	5	5	5
7	f	3	3	3	3	3	3	3	3
8	g	1	1	1	1	1	1	1	1
9	h	4	4	4	4	4	4	4	4
10	i	2	2	2	2	2	2	2	2
11	a	4	4	4	4	4	4	4	4
12	k	5	5	5	5	5	5	5	5
13	l	3	3	3	3	3	3	3	3
14	m	1	1	1	1	1	1	1	1
15	b	4	4	4	4	4	4	4	4
16	o	2	2	2	2	2	2	2	2
17	p	4	4	4	4	4	4	4	4
18	q	5	5	5	5	5	5	5	5
values

 

[kvsrinivasamurthy]

In B2 copied to full range

=IFERROR(INDEX(values!$B$2:$I$18,MATCH(country!A2,values!$A$2:$A$18,0),MATCH(country!B$1,values!$B$1:$I$1,0)),"")

 

[etaf]

=SUMPRODUCT((values!$A$2:$A$18=$A2)*(values!$B$1:$I$1=B$1)*(values!$B$2:$I$18))

As its numbers and also - if A or B appers more than oncve

I changes the values for A and B - so it was not just 4

Book2
	A	B	C	D	E	F	G	H	I
1		jan	feb	mar	apr	may	jun	jul	aug
2	a	1	2	3	4	5	6	7	8
3	b	8	7	6	5	4	3	2	1
Sheet1

Cell Formulas
Range	Formula
B2:I3	B2	=SUMPRODUCT((values!$A$2:$A$18=$A2)*(values!$B$1:$I$1=B$1)*(values!$B$2:$I$18))

Book2
	A	B	C	D	E	F	G	H	I
1		jan	feb	mar	apr	may	jun	jul	aug
2		1	1	1	1	1	1	1	1
3	s	4	4	4	4	4	4	4	4
4	c	2	2	2	2	2	2	2	2
5	d	4	4	4	4	4	4	4	4
6	e	5	5	5	5	5	5	5	5
7	f	3	3	3	3	3	3	3	3
8	g	1	1	1	1	1	1	1	1
9	h	4	4	4	4	4	4	4	4
10	i	2	2	2	2	2	2	2	2
11	a	1	2	3	4	5	6	7	8
12	k	5	5	5	5	5	5	5	5
13	l	3	3	3	3	3	3	3	3
14	m	1	1	1	1	1	1	1	1
15	b	8	7	6	5	4	3	2	1
16	o	2	2	2	2	2	2	2	2
17	p	4	4	4	4	4	4	4	4
18	q	5	5	5	5	5	5	5	5
values

 

[petrutms]

the a, b are unique, i will try noe both solutions and report back. Thanks

 

[etaf]

the a, b are unique

Then just use

kvsrinivasamurthy​

Solution

Although you may need a $ on the A2 lookup as you copy across
=IFERROR(INDEX(values!$B$2:$I$18,MATCH(country!$A2,values!$A$2:$A$18,0),MATCH(country!B$1,values!$B$1:$I$1,0)),"")

 

[petrutms]

Then just use

kvsrinivasamurthy​

Solution

Although you may need a $ on the A2 lookup as you copy across
=IFERROR(INDEX(values!$B$2:$I$18,MATCH(country!$A2,values!$A$2:$A$18,0),MATCH(country!B$1,values!$B$1:$I$1,0)),"")

yes that works fine and that is the only change I did. Thanks for the help all!

 

[MARZIOTULLIO]

country
B2=IF(AND(country!A2<>"",country!B$1<>""),SUMIF(values!$A$2:$A$18,country!$A2,INDEX(values!$B$2:$I$18,,MATCH(country!B$1,values!$B$1:$I$1,0))),"")

Copy across and down

 

[etaf]

glad you have a solution