Reverse INDEX MATCH MATCH search

[Alhelor]

Honestly, it's hard to believe a solution as easy as the one I'm asking for doesn't exist on the internet (spent 1h googling), but here it goes:

Consider this table:

Now, if we wanted to look up what a combination of R1 and C1 gives us, that would be a simple INDEX MATCH MATCH.

But what if we know R1, A2, and want the function to return C1, C3? How do you do this "reverse search"?

Please help

 

[63falcondude]

Try this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		C1	C2	C3	C4		Y-Value	R1		X-Values
2	R1	A2	A1	A2	A3		Table Value	A2		C1
3	R2	A1	A2	A1	A1					C3
4	R3	A1	A1	A1	A3
5	R4	A2	A2	A1	A3
Sheet1

Cell Formulas
Range	Formula
J2	{=IFERROR(INDEX($B$1:$E$1,SMALL(IF(($A$2:$A$5=$H$1)*($B$2:$E$5=$H$2),COLUMN($B$1:$E$1)-(COLUMN($B$1)-1)),ROWS($A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[63falcondude]

Just for the fun of it, you can also mimic the colors in your table through 3 Conditional Formatting rules.

Highlight A2:A5 > Conditional Formatting > Highlight Cell Rules > Equal to > $H$1 > Custom Format > Green Fill > OK > OK

Highlight B1:E1 > Conditional Formatting > New Rule > Use a formula > =COUNTIF($J2:$J5,B1) > Format > Orange Fill > OK > OK

Highlight B2:E5 > Conditional Formatting > New Rule > Use a formula > =AND($A2=$H$1,B2=$H$2) > Format > Blue Fill > OK > OK

The result will look like this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		C1	C2	C3	C4		Y-Value	R2		X-Values
2	R1	A2	A1	A2	A3		Table Value	A1		C1
3	R2	A1	A2	A1	A1					C3
4	R3	A1	A1	A1	A3					C4
5	R4	A2	A2	A1	A3
Sheet1

 

[Alhelor]

Thank you for your responses!

@falcon: while your formula does what it's supposed to do, I want to use that array further to match against other things without "printing" it out. The idea is that the function should return an array that I could use as follows:
SMALL(IF(array=array1_on_some_other_sheet;array2_on_some_other_sheet;"");1)

The way your formula works you can't just replace "array" in the formula above with it. Is there another way?

 

[63falcondude]

Here is an example of finding the first smallest value (based on a lookup table) without producing a list of matching X-Values.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1		C1	C2	C3	C4		Y-Value	R2		X-Value	# Value
2	R1	A2	A1	A2	A3		Table Value	A1		C1	2
3	R2	A1	A2	A1	A1					C2	1
4	R3	A1	A1	A1	A3		Min	2		C3	3
5	R4	A2	A2	A1	A3					C4	4
Sheet1

Cell Formulas
Range	Formula
H4	{=SMALL(IF((A2:A5=H1)*(B2:E5=H2),TRANSPOSE(K2:K5)),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Alhelor]

Hey Falcon, thanks for the response!

However, my real case looks more like this:

So, as you see, it's not sorted/in order. Also, C3 and C1 come up twice, and one of the number cells is empty (and should be disregarded/not taken as 0). The result should be 1.

Any chance you can solve this ultimate task?

 

[63falcondude]

I understand what you are now trying to do but I am not sure how to do it without using the "helper table" in M1:N5.

Excel 2010
	A	B	C	D	E	F	H	I	J	K	L	M	N
1		C1	C2	C3	C4		R1		C3	2		Matches	Min Value
2	R1	A2	A1	A2	A3		A2		C2	1		C1	5
3	R2	A1	A2	A1	A1				F5	5		C3	1
4	R3	A1	A1	A1	A3				C1	5
5	R4	A2	A2	A1	A3				C3	1
6									G8	7
7									C1			Min	1
8									C4	9
Sheet1

Cell Formulas
Range	Formula
N7	=MIN(N2:N5)
N2	{=IF(M2="","",MIN(IF((J$1:J$8=M2)*(K$1:K$8<>""),K$1:K$8)))}
M2	{=IFERROR(INDEX($B$1:$E$1,SMALL(IF(($A$2:$A$5=$H$1)*($B$2:$E$5=$H$2),COLUMN($B$1:$E$1)-(COLUMN($B$1)-1)),ROWS($A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Alhelor]

Thanks for trying, but I need it in one formula, so I can pull it down for multiple tests in a much larger set of data.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

C1​

[/td][td]

C2​

[/td][td]

C3​

[/td][td]

C4​

[/td][td][/td][td]

Y​

[/td][td]

R1​

[/td][td][/td][td]

C3​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

R1​

[/td][td]

A2​

[/td][td]

A1​

[/td][td]

A2​

[/td][td]

A3​

[/td][td][/td][td]

Value​

[/td][td]

A2​

[/td][td][/td][td]

C2​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

R2​

[/td][td]

A1​

[/td][td]

A2​

[/td][td]

A1​

[/td][td]

A1​

[/td][td][/td][td][/td][td][/td][td][/td][td]

F5​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

R3​

[/td][td]

A1​

[/td][td]

A1​

[/td][td]

A1​

[/td][td]

A3​

[/td][td][/td][td="bgcolor:#D9D9D9"]

Result​

[/td][td][/td][td][/td][td]

C1​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

R4​

[/td][td]

A2​

[/td][td]

A2​

[/td][td]

A1​

[/td][td]

A3​

[/td][td][/td][td="bgcolor:#D9D9D9"]

1​

[/td][td][/td][td][/td][td]

C3​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

G8​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

C1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

C4​

[/td][td]

9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in G5
=MIN(IF(ISNUMBER(MATCH($J$1:$J$8,IF(MMULT(COLUMN($B$2:$E$5)^0,($A$2:$A$5=H1)*($B$2:$E$5=H2)),$B$1:$E$1),0)),IF($K$1:$K$8<>"",$K$1:$K$8)))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Marcelo Branco]

I think this simpler formula also works

=MIN(IF(ISNUMBER(MATCH($J$1:$J$8,IF(INDEX($B$2:$E$5,MATCH(H1,$A$2:$A$5,0),0)=H2,$B$1:$E$1),0)),IF($K$1:$K$8<>"",$K$1:$K$8)))
Ctrl+Shift+Enter

M.