Alternative to INDEX/MATCH array

[systematicd]

Hi folks,

First time poster, however I have been stalking the forums on and off since the mid-2000s. I'm getting back into some heavy excel use again and was seeking some advice/opinions on the best way to approach a problem.

Workbook layout:

Sheet 1 - Dataset A
Sheet 2 - Dataset B
Sheet 3 - Primarily index/match array formulae to display conditional values where certain conditions are met.

Dataset A is purchase orders and related info.
Dataset B is quotes. A purchase order may have 0, 1 or multiple quotes.

The first section of Sheet 3 lists purchase orders where certain conditions are met (e.g. value thresholds exceeded). The second section is a matrix that basically looks for the related quotes in Dataset B and displays values if they are found.

The issue I have is with performance of the spreadsheet, especially given Dataset A is massive. Arrays are always painful as you add more calculations.

Just wondering if there are any obvious alternatives I'm missing?

What I did start looking at was using a Power BI relationship between the two sets of data, with that being output into a pivot. Didn't quite get it working as planned.

My constraints are:

- No VBA/Macros
- The output is a 'visual' tool, so I can't have lots of columns. The resulting matrix would rarely need to populate more than three columns of data.
- No MS Access

Cheers,

systematicd

[AliGW]

Welcome to the forum!

As a long-time lurker you will appreciate that we really need a sample workbook.

[FDibbins]

Hi, welcome to the forum

I agree with Ali that a sample WB would help.

The issue I have is with performance of the spreadsheet, especially given Dataset A is massive. Arrays are always painful as you add more calculations.

Are you using full-column references? Dont, if you can help it

- The output is a 'visual' tool, so I can't have lots of columns.

You can always hide columns so they dont show.

[systematicd]

Hopefully a sample workbook is attached? Apologies, some time constraints at the moment but I can post one with working formulae later or tomorrow.

Thanks for the replies

[systematicd]

Are you using full-column references? Dont, if you can help it

No, I don't think so - but great advice. It's someone elses workbook that I have inherited, while I have checked it out - I've not checked this thoroughly.

You can always hide columns so they dont show.

More in reference to 'flattening' data (e.g. using a pivot) - you end up with a bunch of rows/columns that are empty.....but you can't hide it because there is a single cell of data where a match is found.