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!!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]enter address - H3[/TD]
[TD]1 main street[/TD]
[/TR]
[TR]
[TD]enter dept - H4[/TD]
[TD]it[/TD]
[/TR]
[TR]
[TD]ph# is - H6[/TD]
[TD]john1[/TD]
[/TR]
[TR]
[TD]name is - H7[/TD]
[TD]john1[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 288"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]ph#[/TD]
[TD]dept[/TD]
[TD]address[/TD]
[/TR]
[TR]
[TD]john1[/TD]
[TD="align: right"]1[/TD]
[TD]it[/TD]
[TD]1 main street[/TD]
[/TR]
[TR]
[TD]john2[/TD]
[TD="align: right"]2[/TD]
[TD]hr[/TD]
[TD]2 main street[/TD]
[/TR]
[TR]
[TD]john3[/TD]
[TD="align: right"]3[/TD]
[TD]marketing[/TD]
[TD]3 main street[/TD]
[/TR]
[TR]
[TD]john4[/TD]
[TD="align: right"]4[/TD]
[TD]sales[/TD]
[TD]4 main street[/TD]
[/TR]
[TR]
[TD]john5[/TD]
[TD="align: right"]5[/TD]
[TD]admin[/TD]
[TD]5 main street[/TD]
[/TR]
[TR]
[TD]john6[/TD]
[TD="align: right"]6[/TD]
[TD]hr[/TD]
[TD]6 main street[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.