revers lookup duplicated values

L

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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

[TABLE="class: grid, width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]phone#[/TD]
[TD="width: 64"]address[/TD]
[TD="width: 64"]age[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]1[/TD]
[TD]1 main st[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]2[/TD]
[TD]3 east ave[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]lary[/TD]
[TD]3[/TD]
[TD]5 west blvd[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]linda[/TD]
[TD]4[/TD]
[TD]7 main st[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]mike[/TD]
[TD]5[/TD]
[TD]11 east ave[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]6[/TD]
[TD]6 main st[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source[/td][td][/td][td][/td][td][/td][td][/td][td]lookup value[/td][td][/td][td]result[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]name[/td][td=bgcolor:#5B9BD5]phone#[/td][td=bgcolor:#5B9BD5]address[/td][td=bgcolor:#5B9BD5]age[/td][td][/td][td=bgcolor:#5B9BD5]age[/td][td][/td][td=bgcolor:#70AD47]age[/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]phone#[/td][td=bgcolor:#70AD47]address[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]john[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]1 main st[/td][td=bgcolor:#DDEBF7]
40​
[/td][td][/td][td=bgcolor:#DDEBF7]
40​
[/td][td][/td][td=bgcolor:#E2EFDA]
40​
[/td][td=bgcolor:#E2EFDA]john[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]1 main st[/td][/tr]

[tr=bgcolor:#FFFFFF][td]mary[/td][td]
2​
[/td][td]3 east ave[/td][td]
50​
[/td][td][/td][td][/td][td][/td][td]
40​
[/td][td]linda[/td][td]
4​
[/td][td]7 main st[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]lary[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]5 west blvd[/td][td=bgcolor:#DDEBF7]
60​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]linda[/td][td]
4​
[/td][td]7 main st[/td][td]
40​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]mike[/td][td=bgcolor:#DDEBF7]
5​
[/td][td=bgcolor:#DDEBF7]11 east ave[/td][td=bgcolor:#DDEBF7]
20​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]sue[/td][td]
6​
[/td][td]6 main st[/td][td]
30​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
[TABLE="class: grid, width: 720"]
<tbody>[TR]
[TD="width: 80"][/TD]
[TD="width: 80, align: center"]A[/TD]
[TD="width: 80, align: center"]B[/TD]
[TD="width: 80, align: center"]C[/TD]
[TD="width: 80, align: center"]D[/TD]
[TD="width: 80, align: center"]E[/TD]
[TD="width: 80, align: center"]F[/TD]
[TD="width: 80, align: center"]G[/TD]
[TD="width: 80, align: center"]H[/TD]
[/TR]
[TR]
[TD="width: 80"]1[/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[/TR]
[TR]
[TD="width: 80"]2[/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[/TR]
[TR]
[TD="width: 80"]3[/TD]
[TD="width: 80"]
name​
[/TD]
[TD="width: 80, align: center"]phone#[/TD]
[TD="width: 80, align: center"]address[/TD]
[TD="width: 80, align: center"]age[/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80, align: center"]age[/TD]
[TD="width: 80, align: center"]Names[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]john[/TD]
[TD="align: right"]1[/TD]
[TD]1 main st[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD]john[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]mary[/TD]
[TD="align: right"]2[/TD]
[TD]3 east ave[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]linda[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]lary[/TD]
[TD="align: right"]3[/TD]
[TD]5 west blvd[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]linda[/TD]
[TD="align: right"]4[/TD]
[TD]7 main st[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]mike[/TD]
[TD="align: right"]5[/TD]
[TD]11 east ave[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]sue[/TD]
[TD="align: right"]6[/TD]
[TD]6 main st[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Array Formula[/TD]
[/TR]
[TR]
[TD]H4[/TD]
[TD]=IFERROR(INDEX($A$1:$A$9, SMALL(IF($D$4:$D$9=$G$4, ROW()), ROW()-3)),"")[/TD]
[/TR]
</tbody>[/TABLE]

To accept you must press Shift + Control + Enter

Note: It is important that data begin in the cell A4
 
Upvote 0
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))),"")


I0Uqymnw.png
 
Upvote 0
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

DanteAmor said:
[TABLE="class: grid, width: 720"]
<tbody>[TR]
[TD="width: 80"][/TD]
[TD="width: 80, align: center"]A[/TD]
[TD="width: 80, align: center"]B[/TD]
[TD="width: 80, align: center"]C[/TD]
[TD="width: 80, align: center"]D[/TD]
[TD="width: 80, align: center"]E[/TD]
[TD="width: 80, align: center"]F[/TD]
[TD="width: 80, align: center"]G[/TD]
[TD="width: 80, align: center"]H[/TD]
[/TR]
[TR]
[TD="width: 80"]1[/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[/TR]
[TR]
[TD="width: 80"]2[/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[/TR]
[TR]
[TD="width: 80"]3[/TD]
[TD="width: 80"]
name​
[/TD]
[TD="width: 80, align: center"]phone#[/TD]
[TD="width: 80, align: center"]address[/TD]
[TD="width: 80, align: center"]age[/TD]
[TD="width: 80"][/TD]
[TD="width: 80"][/TD]
[TD="width: 80, align: center"]age[/TD]
[TD="width: 80, align: center"]Names[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]john[/TD]
[TD="align: right"]1[/TD]
[TD]1 main st[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD]john[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]mary[/TD]
[TD="align: right"]2[/TD]
[TD]3 east ave[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]linda[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]lary[/TD]
[TD="align: right"]3[/TD]
[TD]5 west blvd[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]linda[/TD]
[TD="align: right"]4[/TD]
[TD]7 main st[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]mike[/TD]
[TD="align: right"]5[/TD]
[TD]11 east ave[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]sue[/TD]
[TD="align: right"]6[/TD]
[TD]6 main st[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Array Formula[/TD]
[/TR]
[TR]
[TD]H4[/TD]
[TD]=IFERROR(INDEX($A$1:$A$9, SMALL(IF($D$4:$D$9=$G$4, ROW()), ROW()-3)),"")[/TD]
[/TR]
</tbody>[/TABLE]

To accept you must press Shift + Control + Enter

Note: It is important that data begin in the cell A4
Click to expand...
 
Upvote 0
With this we get the row number that is equal to 40

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

Excel Workbook
ABCDEF
1
2
3namephone#addressageagerow
4john11 main st40404
5mary23 east ave50FALSO
6lary35 west blvd60FALSO
7linda47 main st407
8mike511 east ave20FALSO
9sue66 main st30FALSO
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
ABCDEF
1
2
3namephone#addressageagerow
4john11 main st40404
5mary23 east ave507
6lary35 west blvd60#NUM!
7linda47 main st40#NUM!
8mike511 east ave20#NUM!
9sue66 main st30#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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Lookup to Return Values based on Adjacent Column Search
2
Replies
14
Views
2K
Fluff
Fluff
G
  • Solved
Sort/filter formula used for search formula combobox issue with shorter named values, Formula help for search combobox
Replies
6
Views
832
Akuini
Akuini
L
  • Question Question
vlookup() returning multiple values
Replies
3
Views
545
Joe4
Joe4
L
  • Question Question
Dynamic Match()/Index() (col changing)
Replies
3
Views
567
Michael M
Michael M

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top