reverse lookup - multiple criteria - multiple results

[lezawang]

Hi
I want to do reverse lookup (multiple criteria - returning multiple results). In this case user will enter Address in H3 and dept in H4
then excel will return ph# in cell H6 and name in cell H7.

I highlighted cells H6 and H7 and then wrote the following formula in cell H6

=INDEX(A2:D15,MATCH(1,(H3=D2:D15)*(H4=C2:C15),0),{1,2})

ctrl+shift+enter

but I got "john1" in both cells!!

enter address - H3	1 main street
enter dept - H4	it
ph# is - H6	john1
name is - H7	john1

<tbody>
</tbody>

name	ph#	dept	address
john1	1	it	1 main street
john2	2	hr	2 main street
john3	3	marketing	3 main street
john4	4	sales	4 main street
john5	5	admin	5 main street
john6	6	hr	6 main street

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

 

[GR00007]

The match function is returning the row when the address & department match which is what you want.
The last part of the index function is the column, you have {1,2}. Make it 1 in cell H6, and 2 in cell H7

 

[Marcelo Branco]

I highlighted cells H6 and H7 and then wrote the following formula in cell H6

=INDEX(A2:D15,MATCH(1,(H3=D2:D15)*(H4=C2:C15),0),{1,2})

ctrl+shift+enter

You are inserting the formula in a vertical range (H6:H7) so try

=INDEX(A2:D15,MATCH(1,(H3=D2:D15)*(H4=C2:C15),0),{1;2})
Ctrl+Shift+Enter

M.

 

[lezawang]

yes the ; fixed the issue. Thank you all very much.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.