Arrays and Text

[gibbsak]

Is there a way to use an array formula in Excel to display text?

I am using several arrays to display cost data for certain parts.
The Arrays I am using look something like this:

DESCRIPTION, TYPE, RATING, COST,
Switch , 1P , 20A , 5 ,

I have had a lot of success in displaying the COST number with the following:
{=SUM(IF((TYPE=A1)*(RATING=B1), COST, 0))}


I would like to streamline the display even further by also having a formula to display the matching text of the DESCRIPTION in a separate column.

I have tried simply using the following with no luck
{=(IF((TYPE=A1)*(RATING=B1), DESCRIPTION, 0))}

What additional function would return the Text or what formula would?

Thank you.

 

[Sandeep Warrier]

Hi & Welcome to the Board!!

Would the Description be the same for a particular combination of Type and Rating?

Taking your example: Would the Description always be Switch for Type = 1P & Rating = 20A ?

If yes, then try

Excel Workbook
	A	B	C	D	E	F	G	H
15	Desc	Type	Rating	Cost		Type	Rating	Desc
16	Switch	1P	20A	5		1P	20A	Switch
17	Plug	2S	20G	9
18	Switch	1P	20A	10
19	Drill	5P	5C	58
20	Switch	1P	20A	15
21	Plug	2S	20G	9
22	Drill	5P	5C	58
23	Switch	1P	20A	20
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Jobowo]

The problem with your current formula is that you are producing an array of description[s if there is more than one match]. If you have only 1 match in each case, you can

- Save the position in the array by using a fake 1:n using ROWS($1:$n) (use an
actual number instead of n)
- Move that to the first position of the array and get at it using SMALL(array,1)
- Use that number in INDEX (description column, n,1)

So (with my 4 rows of data in K,L,M rows 8:11

=INDEX($M$8:$M$11,SMALL(IF(($L$8:$L$11=1)*(K8:K11="Alice"),ROW($1:$4),999),1),1)

It's not a great description but maybe you get the idea?

 

[gibbsak]

Thank you sandeep. That was definately on the right track and was very useful, I am ussing named arrays so I was able to simpilfy in that respect. To answer one of your questions, the Type and Ratings are unique to each description, and there are actually more values on each row to form combinations.

However, is there a way to for the formula to display a blank if there are no matches? Currently I get "#N/A."

Thank you.

 

[Sandeep Warrier]

Thank you sandeep. That was definately on the right track and was very useful, I am ussing named arrays so I was able to simpilfy in that respect. To answer one of your questions, the Type and Ratings are unique to each description, and there are actually more values on each row to form combinations.

However, is there a way to for the formula to display a blank if there are no matches? Currently I get "#N/A."

Thank you.

You're welcome.

To deal with #N/A you could use any of the following confirmed with Control+Shift+Enter:

=IF(ISERROR(Formula),"",Formula)

OR

=IF(ISNA(Formula),"",Formula)

The ISNA() will take care of only #N/A while ISERROR() will take care of various other errors too.