Need help to simplify formula without VBA

[seercoven]

Untitled.jpg

Hi All,

I need help to simplify this formula. As you can see the columns contain kinds of fruit while rows contains different classes.

I need a formula that will show the fruit the kind of classes has. Eg. if i search for class 7, the result will show pineapple & orange. I tried using concentrate to merge but if i have too many column, its complicated.

[JohnTopley]

If you can't use VBA then there is no simply formula as you will have concatenate cells if you require the result in a single cell.

[Tony Valko]

I tried using concentrate to merge but if i have too many column, its complicated.

Which is why you should use a VBA function to do this!

[Toonies]

Hi try this

Class Example.xlsx

Place the following in E2 and copy down

Please Login or Register  to view this content.

I have created a NamedRange of the Class 1 to 10 called ClassList

created Data Validation List in Cell J1

then used following Vlookup

Please Login or Register  to view this content.

to get your required result

This is my best attempt

I'm sure that someone with more knowledge can come up with a better method

Toonies

[seercoven]

Hi toonies, your result is what i wanted. But i have like 70+ column and 2000+ rows. Not possible to add them inside the formula . I don't necessary need the result to shows in one cell as long as i know what are the data in the column. I did try using a pivot table to get but it looks really strange.
Class Example.xlsx

Anyone has other suggestion?

[kvsrinivasamurthy]

Pl see file.
Two ARRAY Formulas
IN G2 then dragged across

Please Login or Register  to view this content.

In N2, then dragged horizontally. In M2 you can put any class required.

Please Login or Register  to view this content.

If all results are required in single cell UDF is required.

ARRAY formulas are tobe confirmed with Ctrl+Shift+Enter, not with only Enter key.

[Toonies]

Ok have a look at this


Class Example v1a.xlsx

it covers 100 Columns and 1500 Rows you can amend it to your requirements

I have replaced the Concatenate formula with the following in CX2 copy down

Please Login or Register  to view this content.

then use a Index Match in DA1

Please Login or Register  to view this content.

CY1 is the search critera ie Class 1 etc you can create a dropdown or simply type in

hopefully this should help

[seercoven]

That's the formula i need. Thanks a-lot and everyone for the one.

[Toonies]

Which one???? as everyone has giving excellent solutions