Double lookup backwards

[Jaymond Flurrie]

I have a target table and a list of values. The table should be filled based on the list.

Here's what it is normally: Two Way Lookup In Excel

So I have that one backwards:

And the source list is in the M1:M4. And the target is A1:K12. Both of these can be a lot bigger too (the list can have, say, 10000 values and the table can be 2000 rows. The column size is at least on the source list fixed).
The cells in A1:K12 should have some kind of formula, that reads from the source list that whether the given cell has a value or not. So, cell B2 result should be 5, cell C2 should result should be 3 and cell D3 result should be 8. The same row-column value shouldn't be defined more than once in the M1:O4 list.

This is trivial to do with VBA. But how about with Excel-formulas? I was trying something with VLOOKUP and INDEX-MATCH, but didn't really get a good solution with those.

So again, looking for a formula for those A1:K12 cells.

I'm using Excel 2016.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
2	r1	5	3										r1	c1	5
3	r2			8									r1	c2	3
4	r3												r2	c3	8
5	r4
6	r5
7	r6
8	r7
9	r8
10	r9
11	r10
12	r11
Sheet4

Cell Formulas
Range	Formula
B2:K12	B2	=IFNA(INDEX($O$2:$O$4,MATCH($A2&"|"&B$1,$M$2:$M$4&"|"&$N$2:$N$4,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Another variation, with normal entry.

23 06 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
2	r1	5	3										r1	c1	5
3	r2			8									r1	c2	3
4	r3												r2	c3	8
5	r4
6	r5
7	r6
8	r7
9	r8
10	r9
11	r10
12	r11
Lookup

Cell Formulas
Range	Formula
B2:K12	B2	=IFERROR(INDEX($O:$O,AGGREGATE(15,6,ROW($O$2:$O$200)/(($M$2:$M$200=$A2)*($N$2:$N$200=B$1)),1)),"")

 

[Jaymond Flurrie]

Another variation, with normal entry.

23 06 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
2	r1	5	3										r1	c1	5
3	r2			8									r1	c2	3
4	r3												r2	c3	8
5	r4
6	r5
7	r6
8	r7
9	r8
10	r9
11	r10
12	r11
Lookup

Cell Formulas
Range	Formula
B2:K12	B2	=IFERROR(INDEX($O:$O,AGGREGATE(15,6,ROW($O$2:$O$200)/(($M$2:$M$200=$A2)*($N$2:$N$200=B$1)),1)),"")

At least this worked great, thank you

 

[Fluff]

Are you saying mine didn't?

 

[Jaymond Flurrie]

Are you saying mine didn't?

I'm saying I took the later one, because that seemed to already do what I needed without array formulas. It doesn't make yours any worse.

 

[Fluff]

From the way you said

At least this worked great

I thought you were saying that my suggestion didn't work. That's why I asked.

 

[Jaymond Flurrie]

From the way you said

I thought you were saying that my suggestion didn't work. That's why I asked.

Oh, I can understand how that can be seen that way. Yeah, specifically meant that we have here at the very least one well working solution, but there might just as easily be already two.