Forum Discussion
Solved
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.
| Bank | Currency | Post Date | Amount |
| ANZ | AUD | 1/07/2023 | -10258.50 |
| ANZ | AUD | 1/07/2023 | -19.80 |
| CBA | AUD | 1/07/2023 | 16500.00 |
| CBA | AUD | 1/07/2023 | 365.44 |
| BNZ | NZD | 1/07/2023 | -0.60 |
| BNZ | NZD | 1/07/2023 | -13604.32 |
| BNZ | NZD | 1/07/2023 | -39.87 |
| BNZ | NZD | 1/07/2023 | -95720.00 |
| BNZ | NZD | 1/07/2023 | 15700.00 |
| ANZ | AUD | 2/07/2023 | -11400.00 |
| ANZ | AUD | 2/07/2023 | 16185.00 |
| ANZ | AUD | 2/07/2023 | 2200.00 |
| CBA | AUD | 2/07/2023 | 33997.05 |
| BNZ | NZD | 2/07/2023 | 17450.00 |
| ANZ | AUD | 3/07/2023 | -33539.95 |
| ANZ | AUD | 3/07/2023 | 869.71 |
| ANZ | AUD | 3/07/2023 | 891.00 |
| ANZ | AUD | 3/07/2023 | 2200.00 |
| ANZ | AUD | 3/07/2023 | 6336.00 |
| ANZ | AUD | 3/07/2023 | 477.77 |
| CBA | AUD | 3/07/2023 | 35200.00 |
| BNZ | NZD | 3/07/2023 | 495.90 |
Thanks in advance for your help
Rick
3 Replies
I'm not sure if I understood it correctly, but here's an example (with Excel 2016)...maybe that's what you're looking for
.- Yes this works but is quite complicated. Thanks.
