"Inside out" multiple table lookup

[phmalu]

If anyone is willing to help, I need a formula that is going to find "Product ID" (in row 2) considering user input product prices.
There are more instructions inside the sheet, right after the table. In F8:F10 I've put the results one'd expect to get using the formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Product id
2		id1	id2	id3	id4	id5	id6	id7	id8	id9	id10
3	Retail price	7.50	12.60	24.40	9.20	9.20	16.01	5.05	3.07	8.68	7.50
4	Production cost	5.00	10.00	20.00	6.00	8.00	14.00	5.00	3.00	7.00	6.00
5
6
7		Price type	Price	Procuct id
8	E.g 1	Production cost	8	??	<- expected result	id5
9	E.g 2	Retail price	7.5	??	<- expected result	id1, id10
10	E.g 3	Retail price	24.5	??	<- expected result	No such product
11
12	Price column (C8:C10) is user input
13	Price type (B8:E10) is selected by user using drop-down
14	Formula (D8:D10) must search for user price input (C8:C10) somewhere in B3:K4 then (if there is any) display matching id within row 2
15	The tricky part is that If more than one result is valid (as shown in E.g 2 - G8), all results must be displayed one after the other [matching id A, matching id B… matching id N]
16	Within my real data, most (but not all) "prices and costs" are unique inside their category. Hence, formula should only account for exact matches.
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B8:B10	List	=$A$3:$A$4

p.s.: consider this table is generated by our internal system so unfortunately changing the way data is displayed should only be considered as a last resort.

Thank you!

 

[diddi]

eg1 does not match your sheet?

 

[Habtest]

Hey there, this should work, though a bit messy. Hope someone to come up with a more elegant solution.

Not sure how many columns are there in your spreadsheet, if more than OP, you may change the column width in two MATCH formulae and expand all references to COLUMN K.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Product id
2		id1	id2	id3	id4	id5	id6	id7	id8	id9	id10
3	Retail price	7.5	12.6	24.4	9.2	9.2	16.01	5.05	3.07	8.68	7.5
4	Production cost	5	10	20	6	8	14	5	3	7	6
5
6
7		Price type	Price	Procuct id
8	E.g 1	Production cost	8	id5	<- expected result	id5
9	E.g 2	Retail price	7.5	id1, id10	<- expected result	id1, id10
10	E.g 3	Retail price	24.5	No such product	<- expected result	No such product
11
Sheet2

Cell Formulas
Range	Formula
D8:D10	D8	=IFERROR(LEFT(CONCAT(IF(OFFSET($A$2,MATCH($B8,$A$3:$A$4,0),0,1,11)=$C8,$A$2:$K$2&", ","")),LEN(CONCAT(IF(OFFSET($A$2,MATCH($B8,$A$3:$A$4,0),0,1,11)=$C8,$A$2:$K$2&", ","")))-2),"No such product")

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Product id
2		id1	id2	id3	id4	id5	id6	id7	id8	id9	id10
3	Retail price	7.5	12.6	24.4	9.2	9.2	16.01	5.05	3.07	8.68	7.5
4	Production cost	5	10	20	6	8	14	5	3	7	6
5
6
7		Price type	Price	Procuct id
8	E.g 1	Production cost	8	id5
9	E.g 2	Retail price	7.5	id1, id10
10	E.g 3	Retail price	24.5	No such product
11
Main

Cell Formulas
Range	Formula
D8:D10	D8	=IF(TEXTJOIN(", ",,IF(($A$3:$A$4=B8)*($B$3:$K$4=C8),$B$2:$K$2,""))="","No such product",TEXTJOIN(", ",,IF(($A$3:$A$4=B8)*($B$3:$K$4=C8),$B$2:$K$2,"")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[phmalu]

Hey there, this should work, though a bit messy. Hope someone to come up with a more elegant solution.

Not sure how many columns are there in your spreadsheet, if more than OP, you may change the column width in two MATCH formulae and expand all references to COLUMN K.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Product id
2		id1	id2	id3	id4	id5	id6	id7	id8	id9	id10
3	Retail price	7.5	12.6	24.4	9.2	9.2	16.01	5.05	3.07	8.68	7.5
4	Production cost	5	10	20	6	8	14	5	3	7	6
5
6
7		Price type	Price	Procuct id
8	E.g 1	Production cost	8	id5	<- expected result	id5
9	E.g 2	Retail price	7.5	id1, id10	<- expected result	id1, id10
10	E.g 3	Retail price	24.5	No such product	<- expected result	No such product
11
Sheet2

Cell Formulas
Range	Formula
D8:D10	D8	=IFERROR(LEFT(CONCAT(IF(OFFSET($A$2,MATCH($B8,$A$3:$A$4,0),0,1,11)=$C8,$A$2:$K$2&", ","")),LEN(CONCAT(IF(OFFSET($A$2,MATCH($B8,$A$3:$A$4,0),0,1,11)=$C8,$A$2:$K$2&", ","")))-2),"No such product")

I might be a bit messy but it works, so thank you!
I surely appreciate your willingness to help me!

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Product id
2		id1	id2	id3	id4	id5	id6	id7	id8	id9	id10
3	Retail price	7.5	12.6	24.4	9.2	9.2	16.01	5.05	3.07	8.68	7.5
4	Production cost	5	10	20	6	8	14	5	3	7	6
5
6
7		Price type	Price	Procuct id
8	E.g 1	Production cost	8	id5
9	E.g 2	Retail price	7.5	id1, id10
10	E.g 3	Retail price	24.5	No such product
11
Main

Cell Formulas
Range	Formula
D8:D10	D8	=IF(TEXTJOIN(", ",,IF(($A$3:$A$4=B8)*($B$3:$K$4=C8),$B$2:$K$2,""))="","No such product",TEXTJOIN(", ",,IF(($A$3:$A$4=B8)*($B$3:$K$4=C8),$B$2:$K$2,"")))
Press CTRL+SHIFT+ENTER to enter array formulas.

A great solution as always Fluff, thank you once again!
In this case it's a matter of curiosity more than anything else, but do you think it would be possible to tweak your formula slightly so no array is necessary in the same time that it uses no volatile functions (specially OFFSET and INDIRECT)? That's because I've had in the past optimization issues when using those in big sheets...
Thank you!

 

[Fluff]

I cannot think of a formula that would do that with your limitations.