SOLVED

# Return next value in list matching criteria

Copper Contributor

# 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

Rick

3 Replies

# Re: Return next value in list matching criteria

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 .

best response confirmed by richarddoig (Copper Contributor)
Solution

# Re: Return next value in list matching criteria

@richarddoig Are you looking for FILTER() function.

``=FILTER(D:D,A:A=G2)``

# Re: Return next value in list matching criteria

Yes this works but is quite complicated. Thanks.
1 best response

Accepted Solutions
best response confirmed by richarddoig (Copper Contributor)
Solution

# Re: Return next value in list matching criteria

@richarddoig Are you looking for FILTER() function.

``=FILTER(D:D,A:A=G2)``