MS Excel ARRAY formula 1

[ceecld]

Hi guys,

Im trying to return a text string from an array formula which is using an if statement to determine the required data. a simplified example of the formula is shown below:

{=(IF(((F16:F19="m")*(E16:E19=132)),D1619))}

i would be able to get this formulae to work if i was trying to return a number, as i would i would use either max or sum infront of the if, e.g.

{=SUM(IF(((F16:F19="m")*(E16:E19=132)),D1619))}

i cant work out though how to return a text string using this method though.

Cheers

 

[Hasit]

Not sure I understand what you are trying to do, but would VLOOKUP not give you what you need?

 

[SkipVought]

Hi,

Maybe you could explain what you goal is.

Given WHAT criteria, in WHAT ranges, return WHAT? Seems like a lookup of some kind: VLOOKUP, INDEX & MATCH.

A specific example would go a long way.

Skip,

_[red][/red]

 

[GlennUK]

How about:

=INDEX(D1:D999,SUMPRODUCT((E16:E19=132)*(F16:F19="m")*ROW(E16:E19)))

assuming only one row matches what you want.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ceecld]

cheers guys

i becomes a bit complex for vlookups, however i think you are right with the index function, to be honest ive never tried to use it before so ill give it a wirl and if i get stuck ill wave my white flag again.

 

[ceecld]

Cheers Glenn, works like a dream!

any chance of a breif explanation of how that little function works?

 

[GlennUK]

Great! Glad that works.

The INDEX function returns the Nth item in a range, and the SUMPRODUCT function works like an array formula. I fed the logical tests you wanted into the SUMPRODUCT ( multiplying the tests ) together with a usage of the ROW function to return the row numbers of the parts being tested. So the SUMPRODUCT returns the row number where E16:E19=132 and where F16:F19="m". The INDEX function is refering to column D, starting at row 1, so the calculated row number returns exactly what you want.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ceecld]

Cheers Glenn

 

[ceecld]

Okay having played about with it for a few minutes i see that it turns the criteria into true or false values (1 or 0) and returns the row number based on the criteria... i like it!

thanks again

 

[KenWright]

A wealth of detail as to how SUMPRODUCT does it's thing:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------