revers lookup duplicated values

[lezawang]

Hi
I have table like below, user will enter age and I want excel to return the name, so I did match/index function like this

=INDEX(A2:D7,MATCH(G4,D2:D7,0),1)

G4 = age

Now if user enter G4 = 40 excel will return john, how can I ask excel to return linda as well. I am thinking it must be array formula, am I right? Thank you

name	phone#	address	age
john	1	1 main st	40
mary	2	3 east ave	50
lary	3	5 west blvd	60
linda	4	7 main st	40
mike	5	11 east ave	20
sue	6	6 main st	30

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[sandy666]

try PowerQuery

source					lookup value		result
name	phone#	address	age		age		age	name	phone#	address
john	1​	1 main st	40​		40​		40​	john	1​	1 main st
mary	2​	3 east ave	50​				40​	linda	4​	7 main st
lary	3​	5 west blvd	60​
linda	4​	7 main st	40​
mike	5​	11 east ave	20​
sue	6​	6 main st	30​

[SIZE=1]let
    Source = Table.NestedJoin(Table5,{"age"},Table6,{"age"},"Table6",JoinKind.LeftOuter),
    #"Expanded Table6" = Table.ExpandTableColumn(Source, "Table6", {"name", "phone#", "address"}, {"name", "phone#", "address"})
in
    #"Expanded Table6"[/SIZE]

 

[DanteAmor]

	A	B	C	D	E	F	G	H
1
2
3	name​	phone#	address	age			age	Names
4	john	1	1 main st	40			40	john
5	mary	2	3 east ave	50				linda
6	lary	3	5 west blvd	60
7	linda	4	7 main st	40
8	mike	5	11 east ave	20
9	sue	6	6 main st	30

<tbody>
</tbody>

Cell	Array Formula
H4	=IFERROR(INDEX($A$1:$A$9, SMALL(IF($D$4:$D$9=$G$4, ROW()), ROW()-3)),"")

<tbody>
</tbody>

To accept you must press Shift + Control + Enter

Note: It is important that data begin in the cell A4

 

[aqeelnokia99]

Formula :=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/($D2=$D$2:$D$7),COLUMNS($D$2:D2))),"")

 

[lezawang]

Thank you all for your help. Could you please break down the formula, specially this part

SMALL(IF($D$4:$D$9=$G$4, ROW()), ROW()-3)),"")

Thank you once again

	A	B	C	D	E	F	G	H
1
2
3	name​	phone#	address	age			age	Names
4	john	1	1 main st	40			40	john
5	mary	2	3 east ave	50				linda
6	lary	3	5 west blvd	60
7	linda	4	7 main st	40
8	mike	5	11 east ave	20
9	sue	6	6 main st	30

<tbody>
</tbody>

Cell	Array Formula
H4	=IFERROR(INDEX($A$1:$A$9, SMALL(IF($D$4:$D$9=$G$4, ROW()), ROW()-3)),"")

<tbody>
</tbody>

To accept you must press Shift + Control + Enter

Note: It is important that data begin in the cell A4

 

[DanteAmor]

With this we get the row number that is equal to 40

IF($D$4:$D$9=$G$4, ROW())

Excel Workbook
	A	B	C	D	E	F
1
2
3	name	phone#	address	age	age	row
4	john	1	1 main st	40	40	4
5	mary	2	3 east ave	50		FALSO
6	lary	3	5 west blvd	60		FALSO
7	linda	4	7 main st	40		7
8	mike	5	11 east ave	20		FALSO
9	sue	6	6 main st	30		FALSO
Hoja1

The SMAL Function will help us sort the data in ascending order. The first argument of the function is the same formula as in the previous step and the second argument is the Row function that will help us indicate the position we need:

Excel Workbook
	A	B	C	D	E	F
1
2
3	name	phone#	address	age	age	row
4	john	1	1 main st	40	40	4
5	mary	2	3 east ave	50		7
6	lary	3	5 west blvd	60		#NUM!
7	linda	4	7 main st	40		#NUM!
8	mike	5	11 east ave	20		#NUM!
9	sue	6	6 main st	30		#NUM!
Hoja1

You can see that the second argument of the SMALL function is the function FILA minus 3. The reason for this operation is that the data starts in the fourth row of the sheet but we need to make an adjustment, through the subtraction, so that it starts by number 1.

 

[lezawang]

I got it. Thank so so much.

 

[DanteAmor]

You're welcome.