Arrays and Text

gibbsak

New Member
Joined
Nov 24, 2009
Messages
12
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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
ABCDEFGH
15DescTypeRatingCostTypeRatingDesc
16Switch1P20A51P20ASwitch
17Plug2S20G9
18Switch1P20A10
19Drill5P5C58
20Switch1P20A15
21Plug2S20G9
22Drill5P5C58
23Switch1P20A20
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

Board Regular
Joined
Oct 21, 2009
Messages
242
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

New Member
Joined
Nov 24, 2009
Messages
12
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

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top