Match 2 values in a different sheet with page number to find return value.

[sneakyla]

Hello!

Can anyone help me find the right formula for what I need?

I have multiple sheets that has a continuous name starting from 100 to 200.

I'm currently using a formula to show the sheet name inside the work page in one of the cell. (see below in 2nd sheet corner "sheet 100" is the sheet name and so on)

I'm trying to use 1 sheet to input all the information and have the rest of the sheets from 100 to 200 receive information. This sheet is called INFO SHEET

I need it to match the sheet name, the fruit and size and return value.

EXAMPLES BELOW.
______________________________________________________________________________________

INFO SHEET BELOW.

		sheet 100	sheet 101	sheet 102
apple	small	2​	1​	7​
apple	medium	4​	3​	9​
apple	large
orange	small
orange	medium
orange	large	9
pear	small
pear	medium
pear	large

2ND SHEET BELOW (WHERE IT NEEDS THE FORMULA)

					sheet 100
	small	medium	large
apple	(need formula to find value of 2 from info sheet)​	(need formula to find value of 4 from info sheet)​	​
orange	​	​	(need formula to find value of 9 from info sheet)​
pear

Thank you!

 

[etaf]

should the sheet 100 be in a different position OR do you change that entry to lookup a different result

A grid lookup may work - i'll put something together assuming Column F - row 1 (F1) has the sheet name

 

[etaf]

here

=INDEX(Info!$C$3:$E$11,MATCH($A5&B$4,Info!$A$3:$A$11&Info!$B$3:$B$11,0),MATCH(out!$F$2,Info!$C$2:$E$2,0))

will work with 2016 version , with later version 365 - maybe able to use Filter .....

Book3
	A	B	C	D	E	F
1
2						sheet 100
3
4		small	medium	large
5	apple	2	4	0
6	orange	0	0	9
7	pear	0	0	0
8
out

Cell Formulas
Range	Formula
B5:D7	B5	=INDEX(Info!$C$3:$E$11,MATCH($A5&B$4,Info!$A$3:$A$11&Info!$B$3:$B$11,0),MATCH(out!$F$2,Info!$C$2:$E$2,0))

Book3
	A	B	C	D	E
1
2			sheet 100	sheet 101	sheet 102
3	apple	small	2	1	7
4	apple	medium	4	3	9
5	apple	large
6	orange	small
7	orange	medium
8	orange	large	9
9	pear	small
10	pear	medium
11	pear	large
Info

I have added a dropbox link - will only be available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[sneakyla]

here

=INDEX(Info!$C$3:$E$11,MATCH($A5&B$4,Info!$A$3:$A$11&Info!$B$3:$B$11,0),MATCH(out!$F$2,Info!$C$2:$E$2,0))

will work with 2016 version , with later version 365 - maybe able to use Filter .....

Book3
	A	B	C	D	E	F
1
2						sheet 100
3
4		small	medium	large
5	apple	2	4	0
6	orange	0	0	9
7	pear	0	0	0
8
out

Cell Formulas
Range	Formula
B5:D7	B5	=INDEX(Info!$C$3:$E$11,MATCH($A5&B$4,Info!$A$3:$A$11&Info!$B$3:$B$11,0),MATCH(out!$F$2,Info!$C$2:$E$2,0))

Book3
	A	B	C	D	E
1
2			sheet 100	sheet 101	sheet 102
3	apple	small	2	1	7
4	apple	medium	4	3	9
5	apple	large
6	orange	small
7	orange	medium
8	orange	large	9
9	pear	small
10	pear	medium
11	pear	large
Info

I have added a dropbox link - will only be available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

It worked!

Thank you so much! Cheers!

 

[etaf]

you are welcome