Lookup formula to return several options

[Kazdima]

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

 

[Aladin Akyurek]

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))),"")

 

[Kazdima]

Your formula is not working. It gives wrong reference and only to one cell. This my adapted formula.

.=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))),"")

<colgroup><col width="270"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Your formula is not working. It gives wrong reference and only to one cell.[…]

Book1
	C	D	E	F
1			Client	Amount
2			Rohan-Hintz	$395.70
3	330.5		Herzog LLC	$375.50
4			Adams and Sons	$225.20
5	Wuckert, Mertz and Reichel		Raynor LLC	$385.20
6	aladin		Wuckert, Mertz and Reichel	$330.80
7	dan		Ziemann and Sons	$465.80
8			McDermott, Lubowitz and Heathcote	$300.90
9			Sauer-Schinner	$490.30
10			Moore, Altenwerth and Haley	$255.30
11			Runolfsson, Parker and Berge	$360.50
12			Eichmann LLC	$345.60
13			Kuhn-Baumbach	$325.60
14			Schmitt-Rodriguez	$246.00
15			Kertzmann-Roob	$355.40
16			Becker and Sons	$235.10
17			Doyle and Sons	$205.80
18			Jacobs Inc	$490.90
19			Corkery, Wunsch and O'Connell	$410.70
20			Barrows Inc	$240.90
21			Veum-Fritsch	$275.50
22			aladin	$330.75
23			dan	$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.

 

[Kazdima]

Thank you- It is working

 

[Aladin Akyurek]

Thank you- It is working

You are welcome.