Return Multiple Values Horizontally - INDEX/IF/ROW/SMALL?

[Fyresparxx]

I'm trying to set up a quick reference list and I need to list equipment across the page based on their status.

- Column A lists what room the equipment is in.
- An "X" in B indicates that the equipment is down.
- C lists the name of the piece of equipment.

	A	B	C	D	E	F	G
1	RM1		EQPT1		Check:
2	RM1	X	EQPT2		EQPT2	EQPT3	EQPT5
3	RM1	X	EQPT3
4	RM1		EQPT4
5	RM1	X	EQPT5
6	RM1		EQPT6
7	RM2		EQPT7		Check:
8	RM2	X	EQPT8		EQPT8
9	RM2		EQPT9
10	RM3		EQPT10		ALL GOOD

<tbody>
</tbody>

My actual sheet is much longer, and the room numbers are each a merged cell, color coded and numbered in A.

Basically, on the first line for each room I have an "IF" function that essentially says "good" or "check." That works.
Underneath I HAD an INDEX formula copied across the page that listed each piece of that room's equipment by name if it was due for maintenance, and it showed up like my example above. Some of these rooms have 30 or so pieces of equipment in them, and there are several rooms in the facility.

My formula got deleted and I can NOT figure out how I did it again. I need to check B for "X" and return all C values in a row.

The old formula was an INDEX formula using ROW and SMALL to return multiple values, but I can't figure it out again.

I do not want to enter a bunch of "IF(B="x"),C" formulas; That could result in a piece of equipment listing off the page and being missed. the point of this sheet is to have an "at a glance" look at what needs maintenance.

 

[Aladin Akyurek]

Please try to be concise. Are you wanting to list equipments which are down, i.e., marked with X?

 

[FDibbins]

From what I can make out, maybe this ARRAY formula, copied down...
=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($A$1:$A$10)),ROWS($A$1:A1))),"")
entered using CTRL SHIFT ENTER, not just enter

 

[Fyresparxx]

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($A$1:$A$10)),ROWS($A$1:A1))),"") works, except that I need to list equipment horizontally rather than vertically (for reporting printouts). I can''t seem to figure out how I was able to get it to list horizontally before.

To be more concise, I'm looking for a formula to put into the bold areas of my example, listing only equipment marked with "x".

 

[Fyresparxx]

Update.
the ARRAY =IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($A$1:$A$10)),COLUMN(A1))),"") Almost does what I need it to. It works for the first room only though, and I can't figure out why. If I change it to =IFERROR(INDEX($C$11:$C$20,SMALL(IF($B$11:$B$20="x",ROW($A$11:$A$20)),COLUMN(A1))),"") for use on the next room it only returns blank values regardless of what is entered in B. Removing IFERROR reveals a #REF! error. Am I missing something?

I think something is actually wrong with my excel. It took me a while to figure out. I thought the COLUMN change should have fixed it, but excel kept going between either returning illogical results (EQPT1, EQPT3, EQPT4) to returning results as ROWS even after the COLUMN change, or returning everything as blank (presumably due to IFERROR) until it finally told me "Cannot change part of an ARRAY" and crashed. After a restart, I entered the same formula again to continue trying to make it work and it just worked like it was supposed to. This problem might not have ever been just a formula error in the first place.

 

[XOR LX]

Hi.

The construction you were given was not a very rigorous or flexible one.

Try:

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($C$1:$C$10)-MIN(ROW($C$1:$C$10))+1),COLUMNS($A:A))),"")

and copy to the right.

Now you can adjust the range being referenced (C1:C10) to whatever you wish and the formula will continue to give correct results, which was not the case with the version you were given previously.

Also, if the size of your dataset is actually not just 10 rows' worth but potentially much larger, then this IFERROR approach will cost you quite heavily in terms of resource required. Let me know and I will present you with a more efficient set-up.

Regards

 

[Fyresparxx]

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($C$1:$C$10)-MIN(ROW($C$1:$C$10))+1),COLUMNS($A:A))),"")
is exactly what I needed. Thank you very much! Now that I see it again I do remember that I used MIN once upon a time, but I completely forgot about it this time. Thank you again! The dataset as a whole is large, but each array only calculates at most around 30 entries, with most rooms being 8-10. There are a number of ARRAy formulas on the page, but none of them are very large. THis shouldn't be too hard on resources, right?

 

[Aladin Akyurek]

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($C$1:$C$10)-MIN(ROW($C$1:$C$10))+1),COLUMNS($A:A))),"")
is exactly what I needed. Thank you very much! Now that I see it again I do remember that I used MIN once upon a time, but I completely forgot about it this time. Thank you again! The dataset as a whole is large, but each array only calculates at most around 30 entries, with most rooms being 8-10. There are a number of ARRAy formulas on the page, but none of them are very large. THis shouldn't be too hard on resources, right?

MIN has nothing to do with it...

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($C$1:$C$10)-ROW($C$1)+1),COLUMNS($A:A))),"")

will also succeed...

 

[XOR LX]

You're welcome.

I should have clarified - the important factor with regards efficiency with such constructions is not so much the size of the dataset, but more the number of rows to which the array formula is being copied, so as to capture all possible returns. If this number is quite small, then you do not have to worry, and the IFERROR set-up will be fine.

See here for an elaboration if you're interested:

microsoft excel - Look up a value in a list and return ALL multiple corresponding values - Super User

Regards

 

[XOR LX]

MIN has nothing to do with it...

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10="x",ROW($C$1:$C$10)-ROW($C$1)+1),COLUMNS($A:A))),"")

will also succeed...

Of course, but the way you have phrased your opening line would seem to suggest that the variation with MIN is not acceptable, which of course is not at all the case: despite the extra function call, this is still a perfectly valid approach, and one which also lends itself well to the use of Named Ranges, which your version does not, unless the OP happens to know how to use INDEX in such a way so as to create the equivalent of ROW($C$1) from $C$1:$C$10.

Regards