Index / Match with 2 Criteria showing all results

there are many ways to do this ill upload your workbook with some examples

can you use dynamic formulas?

i will use them in my example.


however this is more complex then when i originally read your post as you have duplicates.

ok the problem is for each block of yellow cells you need to adjust the formula.

i have uploaded your book with the solution.

the dynamic ranges can be replaced to suit

Index Match Test.xlsx

no the dynamic by using named ranges (Ctrl+F3) which allows you to enter named ranges, the advantage is that the named ranges can also include formulas.

a dynamic formula will simply grow and shrink with any alterations you make to the list (assuming there are no gaps)

the partition is to aviod any problems where the date and account number link and cause a same look up

all these below would give the same result without the partition
10 1015
101 015
10101 5
1 01015
etc

as when they join they become
101015

obviously you wouldn't want to match these incorrectly. its basically a fail safe to avoid the issue.

No for PAD you would just replace PAD with the range ($A$2:$A$##) where ## is your highest cell number

Please note that the formulas are array formulas so require being entered with ctrl+shift+enter