Return next value in list matching criteria
Hi
I have a list per the below and I want to use a formula to find a value according to the bank criteria.
For example for each bank “BNZ” I want to bring in the corresponding amounts.
When I use the formula =INDEX($A:$A,MATCH(“BNZ”,$D:$D,0)) and increment by 1, it works for the first 5 instances but then it just picks up the next item which is an ANZ entry rather than picking up the next BNZ entry further down the list
The listing is going to change each month depending on what transactions happen each month and there are obviously different numbers of transactions each month.
BankCurrencyPost DateAmountANZAUD1/07/2023-10258.50ANZAUD1/07/2023-19.80CBAAUD1/07/202316500.00CBAAUD1/07/2023365.44BNZNZD1/07/2023-0.60BNZNZD1/07/2023-13604.32BNZNZD1/07/2023-39.87BNZNZD1/07/2023-95720.00BNZNZD1/07/202315700.00ANZAUD2/07/2023-11400.00ANZAUD2/07/202316185.00ANZAUD2/07/20232200.00CBAAUD2/07/202333997.05BNZNZD2/07/202317450.00ANZAUD3/07/2023-33539.95ANZAUD3/07/2023869.71ANZAUD3/07/2023891.00ANZAUD3/07/20232200.00ANZAUD3/07/20236336.00ANZAUD3/07/2023477.77CBAAUD3/07/202335200.00BNZNZD3/07/2023495.90
Thanks in advance for your help
Rick
Hi I have a list per the below and I want to use a formula to find a value according to the bank criteria. For example for each bank “BNZ” I want to bring in the corresponding amounts. When I use the formula =INDEX($A:$A,MATCH(“BNZ”,$D:$D,0)) and increment by 1, it works for the first 5 instances but then it just picks up the next item which is an ANZ entry rather than picking up the next BNZ entry further down the list The listing is going to change each month depending on what transactions happen each month and there are obviously different numbers of transactions each month. BankCurrencyPost DateAmountANZAUD1/07/2023-10258.50ANZAUD1/07/2023-19.80CBAAUD1/07/202316500.00CBAAUD1/07/2023365.44BNZNZD1/07/2023-0.60BNZNZD1/07/2023-13604.32BNZNZD1/07/2023-39.87BNZNZD1/07/2023-95720.00BNZNZD1/07/202315700.00ANZAUD2/07/2023-11400.00ANZAUD2/07/202316185.00ANZAUD2/07/20232200.00CBAAUD2/07/202333997.05BNZNZD2/07/202317450.00ANZAUD3/07/2023-33539.95ANZAUD3/07/2023869.71ANZAUD3/07/2023891.00ANZAUD3/07/20232200.00ANZAUD3/07/20236336.00ANZAUD3/07/2023477.77CBAAUD3/07/202335200.00BNZNZD3/07/2023495.90 Thanks in advance for your helpRick Read More
