How to use an array or matrix to return text vs. numeric values
 

=AVERAGE(IF('Entire Org'!$C$4:$C$4505='Employee Database'!$F1,IF('Entire
Org'!$D$4:$D$4505='Employee Database'!$G1,IF('Entire
Org'!$E$4:$E$4505='Employee Database'!$H1,'Entire Org'!$I$4:$I$4505))))

I am using the above array/matrix to successfully return a numeric value.
Now, I would like to use a very similiar formula (fields, columns) to return
non-numeric values. I have tried to substitute average with cell (and
contents), vlookup, etc, but have failed miserably.

Any thoughts on a formula I can use?

Try this *array* formula:

=INDEX('Entire Org'!$I$4:$I$4505,MATCH(1,('Entire
Org'!$C$4:$C$4505='Employee Database'!$F1)*('Entire
Org'!$D$4:$D$4505='Employee Database'!$G1)*('Entire
Org'!$E$4:$E$4505='Employee Database'!$H1),0))


Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Ingrid" wrote in message
...
=AVERAGE(IF('Entire Org'!$C$4:$C$4505='Employee Database'!$F1,IF('Entire
Org'!$D$4:$D$4505='Employee Database'!$G1,IF('Entire
Org'!$E$4:$E$4505='Employee Database'!$H1,'Entire Org'!$I$4:$I$4505))))

I am using the above array/matrix to successfully return a numeric value.
Now, I would like to use a very similiar formula (fields, columns) to return
non-numeric values. I have tried to substitute average with cell (and
contents), vlookup, etc, but have failed miserably.

Any thoughts on a formula I can use?

One thought, one Comment/Question:

Thought:
I think you could simplify the formula a bit by using an array formula:
=AVERAGE(IF('Entire
Org'!$C$4:$C$4505&'EntireOrg'!$D$4:$D$4505&'Entire Org'!$E$4:$E$4505='Employee
Database'!$F1&'Employee Database'!$G1&'Employee Database'!$H1,'Entire
Org'!$I$4:$I$4505)).

Comment/Question:
The formula that you are asking to build will actually return a 4,401 item
array. The best you could hope to see would be one item in that array.
So...What is it that you are really looking to see from the formula?

Regards,
Ron