Index Match

[Brentsa]

Hi I have a sample Table:

Cashier	Sales Ref	Date:	Product	Total Sales	Tendered	Difference
Harry	5243​	02-02-21​	Apple	9.00	9.00	-
Ben	5244​	03-02-21​	Pear	2.50	2.50	-
Sally	5244​	04-02-21​	Grape	10.00	10.00	-
Gareth	5244​	05-02-21​	Peach	11.25	11.25	-
Beth	5244​	06-02-21​	Lime	9.00	9.00	-
Beth	5244​	07-02-21​	Lemon	21.00	21.00	-
Ben	5244​	08-02-21​	Orange	5.00	9.00	(4.00)
Sally	5244​	09-02-21​	Apple	8.50	8.50	-
Gareth	5244​	10-02-21​	Lime	4.00	4.00	-

I want to run a Index and match formula based on the amount. So if for example I want to match 9.00 I want the result to give me Harry, Beth and Ben. regardless if 9.00 appears in column E or F and I want all options for the 9.00.

just so that you are aware my original data consist of over 1000 rows and I will need to search up to 100 amounts. I normally have the amounts listed in one column and need all the options. I have used a filter formula before but due to different systems being used I need to avoid the Filter Formula.

 

[jasonb75]

Something like this?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	Cashier	Sales Ref	Date:	Product	Total Sales	Tendered	Difference		Amount	Results
2	Harry	5243	02-02-21	Apple	9	9	-		9	Harry	Beth	Ben
3	Ben	5244	03-02-21	Pear	2.5	2.5	-		2.5	Ben
4	Sally	5244	04-02-21	Grape	10	10	-
5	Gareth	5244	05-02-21	Peach	11.25	11.25	-
6	Beth	5244	06-02-21	Lime	9	9	-
7	Beth	5244	07-02-21	Lemon	21	21	-
8	Ben	5244	08-02-21	Orange	5	9	-4
9	Sally	5244	09-02-21	Apple	8.5	8.5	-
10	Gareth	5244	10-02-21	Lime	4	4	-
Sheet8

Cell Formulas
Range	Formula
J2:L3	J2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$10)/((($E$2:$E$10=$I2)+($F$2:$F$10=$I2))>0)/ISNA(MATCH($A$2:$A$10,$I2:I2,0)),1)),"")

 

[Brentsa]

Perfect Thanks a million Jason