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

 

[ExceLoki]

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))

 

[ebea]

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))

 

[Fluff]

How about

=INDEX(INDIRECT("'"&A2&"'!D2:G20"),MATCH(C2&"|"&D2,INDIRECT("'"&A2&"'!B2:B20")&"|"&INDIRECT("'"&A2&"'!C2:C20"),0),{1,2,3,4})

 

[arniebun]

Can't I do a VLOOKUP based on values in column C & D to both match and then reference the other sheet based on the sheet name in a1? like a vlookup based on 2 values, but referencing sheet name in column A

 

[arniebun]

=VLOOKUP(b1&c1,INDIRECT("'"&a1&"'!"&"b2:g6")2,FALSE) something like this but this doesn't seem to work?

 

[ExceLoki]

try this

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

 

[ExceLoki]

try this

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

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))

 

[Fluff]

Can't I do a VLOOKUP based on values in column C & D to both match and then reference the other sheet based on the sheet name in a1? like a vlookup based on 2 values, but referencing sheet name in column A

Did you try the formula I suggested in post#4? It should do exactly what you asked for.

 

[arniebun]

How about

=INDEX(INDIRECT("'"&A2&"'!D2:G20"),MATCH(C2&"|"&D2,INDIRECT("'"&A2&"'!B2:B20")&"|"&INDIRECT("'"&A2&"'!C2:C20"),0),{1,2,3,4})

the eample is a dummy but i can't seem to get it to work on the real one which i can't share because it has work sensative data but if i build my sheet around your formular it works, i will play around with it and see if i can work it out. question the 1234, moves the refrence by 1 when i use 2345, is that how i move the formular to the right to capture more data?