Help - multiconditional index formula

[carlosmaldonado]

Hi guys,

here I'm again to claim for your help.

The issue is that I've to do an INDEX, but I want it to be MULTICONDITIONAL.

So, into the attached excel you can find, sheet (LIST_VALUES_TO_RETREIVE):

The A column contains the date which I intend to consider as a condition ("> 01-12-2013" (EUROPE SETTINGS))
The D column contains the document type which I intend to consider as a condition (only docs started by IMP, via MID function

The column N, contains the Serial number for which I will search and retrieve the value in column U.

Into the sheet (LIST_VALUES_TO_SEARCH), a few examples of serial numbers for which I want to have the WO nr, but this time based on other criteria’s such as DATE and TYPE of document.

Regards
CMP

[Pepe Le Mokko]

Does the attached help ?

[FDibbins]

You can do this with and ARRAY INDEX/MATCH.

If you put the date you want in sheet2 E1 and the Doc No in D1, then use this ARRAY formula in C2, copied down

=INDEX(LIST_VALUES_TO_RETREIVE!$U$1:$U$33,MATCH($C$1&$D$1&A2,LIST_VALUES_TO_RETREIVE!$A$1:$A$33&LIST_VALUES_TO_RETREIVE!$D$1:$D$33&LIST_VALUES_TO_RETREIVE!$N$1:$N$33,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Pepe Le Mokko]

Would an array formula not be much slower than the non-array one I suggested?

( It looks like using array formulas is "hot" these days on the forum )

[FDibbins]

Pepe, I did not look at your file (only saw you posted, after I posted), and yes you are correct, a regular formula will be quicker than an array formula.

I dont particularly like array's I usually prefer to use helpers (I assume thats what you did too?), but this seemed to lend itself to an array formula

[Pepe Le Mokko]

Please Login or Register  to view this content.

No helper needed - I thought I'd raise the point because I see many array solutions where none are needed all over the forum, solutions, which, although probably perfectly valid , could cause some problems when a large spreadsheet containing array formulas would crash, eventually loosing data.

[Tony Valko]

Would an array formula not be much slower than the non-array one I suggested?

You can run some tests and see which formula is faster to calculate. There is calculation timer code at the link below.

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

People seem to think that array formulas are slower to calculate as a general rule of thumb. This is not true but there are many variables to consider. The only way to know for sure is to test the formulas and see which is faster to calculate.

[carlosmaldonado]

Hi Pepe,

thanks a lot for your help as well as the the FDibbins user!

The formula has one small problem which is the date! If I change it, for a date not considered into the criteria, it still retrieves the result!!!

Pls help me!!

Regards
CMP

[Pepe Le Mokko]

Could you please give some more info and an example of what you tried?

[carlosmaldonado]

Hi Pepe,

considering the solution you gave me, the basic test that I've done was changing the date (<"01-12-2013") for a date greater than 01-12-2013, in the line of serial number M91202FC2608, which is the first for which we intend to find the WO nr.

Your formula bellow.
=ÍNDEX(LIST_VALUES_TO_RETREIVE!$U$2:$U$33;SUMPRODUCT((LIST_VALUES_TO_RETREIVE!$A$2:$A$33<"01-12-2013")*(LEFT(LIST_VALUES_TO_RETREIVE!$D$2:$D$33;3)="IMP")*(LIST_VALUES_TO_RETREIVE!$N$2:$N$33=LIST_VALUES_TO_SEARCH!A2)*LIN(LIST_VALUES_TO_RETREIVE!$A$2:$A$33))-1)

Regards
CMP