Lookup formula to return several options

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
226
<article>
Hello,
<article>
recently Chandoo.org posted very good example of Lenient lookup [Advanced Formula Trick].
Formula is working well, but what if I have many the same amounts? How to list all Names with the same amount?
In my case Names= Cheques numbers.
=IFERROR(INDEX(Table3[Names],IFERROR(MATCH($L$3,Table3[Amount],0), MATCH(1, (Table3[Amount]>($L$3-0.5))*(Table3[Amount]<($L$3+0.5)),0)))&IF(COUNTIFS(Table3[Amount],$L$3),"","*"),"Not found")
</article>
Thank you.
</article>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try to adapt the following, which uses the sample data of your link.

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(data[Client],SMALL(IF((data[Amount]>=($C$3-0.5))*(data[Amount]<=($C$3+0.5)),ROW(data)-ROW(INDEX(data,1,1))+1),ROWS($1:1))),"")
 
Upvote 0
Your formula is not working. It gives wrong reference and only to one cell. This my adapted formula.
[TABLE="width: 270"]
<colgroup><col width="270"></colgroup><tbody>[TR]
[TD="class: xl65, width: 270"].=IFERROR(INDEX(DATA!$A$5:$A$5473,SMALL(IF((D5:D5474>=($L$3-0.5))*(D5:D5474<($L$3+0.5)),ROW(data)-ROW(INDEX(data,1,1))+1),ROWS($5:6))),"")[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Your formula is not working. It gives wrong reference and only to one cell.[…]


Book1
CDEF
1ClientAmount
2Rohan-Hintz$395.70
3330.5Herzog LLC$375.50
4Adams and Sons$225.20
5Wuckert, Mertz and ReichelRaynor LLC$385.20
6aladinWuckert, Mertz and Reichel$330.80
7danZiemann and Sons$465.80
8McDermott, Lubowitz and Heathcote$300.90
9Sauer-Schinner$490.30
10Moore, Altenwerth and Haley$255.30
11Runolfsson, Parker and Berge$360.50
12Eichmann LLC$345.60
13Kuhn-Baumbach$325.60
14Schmitt-Rodriguez$246.00
15Kertzmann-Roob$355.40
16Becker and Sons$235.10
17Doyle and Sons$205.80
18Jacobs Inc$490.90
19Corkery, Wunsch and O'Connell$410.70
20Barrows Inc$240.90
21Veum-Fritsch$275.50
22aladin$330.75
23dan$330.50
Sheet1


In C5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(data[Client],SMALL(IF((data[Amount]>=($C$3-0.5))*(data[Amount]<=($C$3+0.5)),ROW(data)-ROW(INDEX(data,1,1))+1),ROWS($1:1))),"")

I believe it works as advertised.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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