reverse lookup - multiple criteria - multiple results

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,565
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 - H31 main street
enter dept - H4it
ph# is - H6john1
name is - H7john1

<tbody>
</tbody>



nameph#deptaddress
john11it1 main street
john22hr2 main street
john33marketing3 main street
john44sales4 main street
john55admin5 main street
john66hr6 main street

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

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,120
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top