Index/match with multiple column criteria: Alternative to array formula and concatenation

[pvp1900]

Hi,
I wanted to use an index function combined with 2 match functions,
whereas both of the match functions refer to different rows.
I want to avoid using a help row where I concatenate both column criteria and also want to avoid using an array function as this consumes CPU.
In column CQ (YELLOW coloured cells only) you see my attempt to nest an "and" function to look up both column criteria without using an array formula or concatenating both column criteria.
For example I want to display in cell CQ7 the value which can be found in CY 1 (columns As to AV) and there matches the criterion "LowerBand1".
Apparently I am always getting the value from column AS

Many thanks in advance
Philip

[tim201110]

as this consumes CPU

not quite
=INDEX($AS10:$BL10,MATCH(1,INDEX(($AS$2:$BL$2=CQ$5)*($AS$3:$BL$3=CI10),),)) not array

[Zer0Cool]

I want to avoid using a help row where I concatenate both column criteria and also want to avoid using an array function as this consumes CPU.

These are basically your 2 options for doing this. The issue is that without concatenation you need to compare when both matches are on the same line, which means comparing multiple cells against multiple other cells (an array). People avoid doing an array formula by using a helping column, concatenating the 2 columns into a single column, thus eliminating the need to compare 2 ranges of cells against each other in an array.

EDIT: Since your data is numeric and your lookup would only return 1 match (presumably) you could also do this with SUMIFS to the same results

EDIT2: For what its worth you have excess styles in your file too. 10,777 to be exact...default is 47. You can get an idea of it from Home | Styles and view the drop down. May want to consider cleaning that up.

EDIT3: Looks like you have a ton of named ranges too. Since I dont know the extent of your file hard to say they are "excess", but I would guess so. 4961 total.

[Richard Buttrey]

Hi,

Why are you so set against a helper row 1 which concatenates rows 2 &3. The whole task would be so much simpler.
Otherwise without a macro I believe you'll l have to have an array formula of one sort or another even if it's only a humble SUMPRODUCT.

Here's one option

Please Login or Register  to view this content.

[Zer0Cool]

Just noticed your headers in row 2 are not repeated. Looks nice for a person, but Excel doesnt know for example that column BJ should be considered CY 5 (i presume).

after fixing that something like this should work:

Please Login or Register  to view this content.

[pvp1900]

HI Zerocool,

thanks a lot for your suggestions reducing CPU consumption.
How can I clean up the Styles?

Regards

Philip

[Zer0Cool]

Sorry, I responded to your PM.

The gist for those reading this. There is not built in way to delete more than 1 style at a time. To do this you either need a 3rd party program or a macro, and most if not all of the macros online do not handle everything like corrupt styles (ones you cant even delete manually).

MS has a page regarding excess styles, and they link to a tool called XLStylesCleaner which has worked well for me in the past. I would of course recommend trying it on a copy of the file first. It also cleans named ranges out.