Vloopup with key value in multiple columns

[wjeil]

Hello quick question i think. I'm tring to return column F in to the corresponding value of B in C from table F:I. It has to match a value in one of the three columns and then return the text in F. I've tried Index and Match and VLookup, but with no succes.

Does someone know the trick?

Map2
	A	B	C	D	E	F	G	H	I
1	A	2				LOC.1	1	2	3
2	B	4				LOC.2	4	5	6
3	C	1				LOC.3	7	8	9
4	D	6				LOC.4	10	11	12
5	E	8
6	F	4
Blad1

 

[wjeil]

I found the solution.

Map2
	A	B	C	D	E	F	G	H	I
1	A	2	LOC.1			LOC.1	1	2	3
2	B	4	LOC.2			LOC.2	4	5	6
3	C	1	LOC.1			LOC.3	7	8	9
4	D	6	LOC.2			LOC.4	10	11	12
5	E	8	LOC.3
6	F	4	LOC.2
7
Blad1

Cell Formulas
Range	Formula
C1:C6	C1	=INDEX($F$1:$F$4,(MATCH(1,MMULT(--($G$1:$I$4=B1),TRANSPOSE(COLUMN($H$1:$J$4)^0)),0)))

 

[Fluff]

You could also use

=FILTER($F$1:$F$4,MMULT(--($G$1:$I$4=B1),SEQUENCE(COLUMNS($G$1:$I$4)))>0)

 

[wjeil]

Good suggestion. That uses less memory

 

[Fluff]

You're welcome & thanks for the feedback.