Reverse lookup

[ExcelNewbie2020]

usually the lookup value is within the single column/rows and the return array can be find from the multiple column/rows..
how can we lookup if the scenario would be like this?

lookup value is from 1 to 15 the return value is from A to D..thanks

A​	1​	2​	3​	4​
B​	5​	6​	7​	8​		LOOKUP VALUE​	6
C​	9​	10​	11​	12​		EXPECTED RESULT​	B
D​	13​	14​	15​	16​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1	2	3	4
3	B	5	6	7	8		LOOKUP VALUE	6
4	C	9	10	11	12		EXPECTED RESULT	B
5	D	13	14	15	16
Main

Cell Formulas
Range	Formula
H4	H4	=INDEX(A:A,AGGREGATE(15,6,ROW(A2:A5)/(B2:E5=H3),1))

 

[ExcelNewbie2020]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1	2	3	4
3	B	5	6	7	8		LOOKUP VALUE	6
4	C	9	10	11	12		EXPECTED RESULT	B
5	D	13	14	15	16
Main

Cell Formulas
Range	Formula
H4	H4	=INDEX(A:A,AGGREGATE(15,6,ROW(A2:A5)/(B2:E5=H3),1))

cool.. thanks man, really appreciated

 

[JvdV]

Book1
	A	B	C	D	E	F	G	H
1	A	1	2	3	4
2	B	5	6	7	8		LOOKUP VALUE	6
3	C	9	10	11	12		EXPECTED RESULT	B
4	D	13	14	15	16
Sheet9

Cell Formulas
Range	Formula
H3	H3	=TOCOL(IF(B:E=H2,A:A,NA()),3)

If multiple cells could be returned and you'd only be interested in the last possible value, then try to the nest above in TAKE():

=TAKE(TOCOL(IF(B:E=H2,A:A,NA()),3),-1)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

If the lookup value will be unique then you could use this H4 formula.
If the lookup value might be repeated and you wanted all relevant 'row headings', perhaps H10

23 01 31.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1	2	3	4
3	B	5	6	7	8		LOOKUP VALUE	6
4	C	9	10	11	12		EXPECTED RESULT	B
5	D	13	14	15	16
6
7
8	A	1	2	3	4
9	B	5	6	7	8		LOOKUP VALUE	6
10	C	6	10	6	12		EXPECTED RESULT	B,C
11	D	13	14	15	16
Lookup

Cell Formulas
Range	Formula
H4	H4	=CONCAT(IF(B2:E5=H3,A2:A5,""))
H10	H10	=TEXTJOIN(",",1,UNIQUE(TOCOL(IF(B8:E11=H9,A8:A11,""))))

@JvdV
I can see my Excel 'think' about those whole column reference formulas.