Match/search formula

[mrafiq44]

I have a large excel with 20000 rows - various transactions - 2 columns one debit and one credit, but they are not in order. Have to search within the large file to match column A to Column B based on the amount. is there a way i can do that without constantly searching via control F and doing find.

 

[RobP]

maybe something like this could help ?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	12	675		11	The +1 in my formula relates to the row offset (as I start at row 2).
3	123	123			Yours could be different depending on how many rows above your data..
4	1245	837
5	532	432
6	432	532
7	543	654
8	234	234
9	654	543
10	345	345
11	675	12
12	837	1245
13
14
Sheet3

Cell Formulas
Range	Formula
D2	D2	=MATCH(A2,$B$2:$B$12,0)+1

needs copying down your data to highlight the corresponding row number for the data

Not sure what you would do if 2 values are the same though ..

 

[RobP]

Or in fact if you have O365, you can use this formula once and it will spll down the rows...

=BYROW(A2:A12,LAMBDA(row,MATCH(row,$B$2:$B$12,0)+1))

Book1
	A	B	C	D
1
2	12	675		11
3	123	123		3
4	1245	837		12
5	532	432		6
6	432	532		5
7	543	654		9
8	234	234		8
9	654	543		7
10	345	345		10
11	675	12		2
12	837	1245		4
Sheet3

Cell Formulas
Range	Formula
D2:D12	D2	=BYROW(A2:A12,LAMBDA(row,MATCH(row,$B$2:$B$12,0)+1))
Dynamic array formulas.