VLOOKUP INDIRECT FROM SHEET, USING 2 criterias

[arniebun]

HI i WOULD LIKE TO VLOOKUP FROM A DIFFRENT SHEET USING 2 VALUES TO LOOKUP EXAMPLE ATTACHED.

I WANT TO LOOK UP COLUM C & B FROM SHEET2 AND POPULATE E,F,G,H, USING INDIRECT TO LOOK UP SHEET IN COLUME A

 

[arniebun]

Book1
	A	B	C	D	E	F	G	H
1	Sheet2	adam	dog	cv1	1	2	3	4
2	Sheet2	adam	bone	cv1	8	6	7	8
3	Sheet2	adam	tree	cv1	5	4	8	9
4	Sheet2	adam	dog	cv2	4	7	8	9
5	Sheet2	adam	bone	cv2	5	6	7	8
6	Sheet2	adam	tree	cv2	3	4	9	8
Sheet1

Cell Formulas
Range	Formula
E1:H6	E1	=INDEX(Sheet2!D$1:D$6,MATCH(1,($C1=Sheet2!$B$1:$B$6)*($D1=Sheet2!$C$1:$C$6),0))

thanks but i need to refference the sheet from

A bit unclear to me, but I guess, so:

Mappe1
	A	B	C	D	E	F	G
1		DOG	CV1	1	2	3	4
2		BONE	CV1	8	6	7	8
3		TREE	CV1	5	4	8	9
4		DOG	CV2	4	7	8	9
5		BONE	CV2	5	6	7	8
6		TREE	CV2	3	4	9	8
sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=LOOKUP(2,1/(sheet2!$B$1:$B$6=$B1)/(sheet2!$C$1:$C$6=$C1),(sheet2!$D$1:$D$6))
E1:E6	E1	=LOOKUP(2,1/(sheet2!$B$1:$B$6=$B1)/(sheet2!$C$1:$C$6=$C1),(sheet2!$E$1:$E$6))
F1:F6	F1	=LOOKUP(2,1/(sheet2!$B$1:$B$6=$B1)/(sheet2!$C$1:$C$6=$C1),(sheet2!$F$1:$F$6))
G1:G6	G1	=LOOKUP(2,1/(sheet2!$B$1:$B$6=$B1)/(sheet2!$C$1:$C$6=$C1),(sheet2!$G$1:$G$6))

i need to reference the sheet name in the cell a1, but thanks for your reply

 

[arniebun]

er sorry, missed one of the sheet refrences

=INDEX(INDIRECT("'"&$A1&"'!"&"$d$1:$d$6"),MATCH(1,($C1=INDIRECT("'"&$A1&"'!"&"$B$1:$B$6"))*($D1=INDIRECT("'"&$A1&"'!"&"$C$1:$C$6")),0))

your last one works as well thanks cant mark both as solved sorry, he got there first but thank you so much

 

[Fluff]

The {1,2,3,4} forces the formula to return all 4 columns from D:G

 

[arniebun]

The {1,2,3,4} forces the formula to return all 4 columns from D:G

lets say i have more colums like 12?

 

[Fluff]

If you have 12 columns you can use

=INDEX(INDIRECT("'"&A2&"'!D2:O20"),MATCH(C2&"|"&D2,INDIRECT("'"&A2&"'!B2:B20")&"|"&INDIRECT("'"&A2&"'!C2:C20"),0),SEQUENCE(,12))

 

[arniebun]

If you have 12 columns you can use

=INDEX(INDIRECT("'"&A2&"'!D2:O20"),MATCH(C2&"|"&D2,INDIRECT("'"&A2&"'!B2:B20")&"|"&INDIRECT("'"&A2&"'!C2:C20"),0),SEQUENCE(,12))

thanks you so much

 

[Fluff]

You're welcome & thanks for the feedback.