Need help solving Excel challenge with related vlaues

I need help solving this Excel challenge (see the attached workbook).

The challenge is to, for a given lookup value in cell F3 ? a supplier ?, return an array of associated items for that supplier.
So if the supplier in F3 is Company X, the result displayed in G3 should be an array with the values {Apples, Pears, Bananas}.

I'm not allowed to use the FILTER-function, which of course should've been the way I would approach this kind of problem.

Do you guys have any ideas?

Even FILTER wouldn't return all the matches to one cell. You can use the formula below. On older versions of Excel, you may have to enter it with CTRL+SHIFT+ENTER to make it an array, not sure, but on 365 you don't have to.

Formula:

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

Whya re you not allowed to use FILTER?

Is this homework or similar??

My guess: not everyone that will use it is on 365.

Sounds like homework !!

To start you on the way, put this formula in cell A3:

=IF(B3=$F$3,MAX(A$2:A2)+1,"-")

then copy down. Can you work out how to then extract the data that corresponds to the numbers generated by the formula?

Incidentally, you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Power Query

Supplier	List
Company X	Apples, Pears, Bananas

Please Login or Register  to view this content.

A co-worker sent us this, and he thought using FILTER would be too easy. Guess he's right.

Originally Posted by Pete_UK

Sounds like homework !!

To start you on the way, put this formula in cell A3:

=IF(B3=$F$3,MAX(A$2:A2)+1,"-")

then copy down. Can you work out how to then extract the data that corresponds to the numbers generated by the formula?

Incidentally, you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Thanks, Pete!

I can't really say that I can work that out. I'll have to pick this formula apart to try to understand

Originally Posted by thomglea

Even FILTER wouldn't return all the matches to one cell. You can use the formula below. On older versions of Excel, you may have to enter it with CTRL+SHIFT+ENTER to make it an array, not sure, but on 365 you don't have to.

Formula:

Please Login or Register  to view this content.

Nice, thank you!

This does the trick, and I understand the formula.
It doesn't have to be an array formula, I just think they're neat.

Originally Posted by sandy666

Power Query

Supplier	List
Company X	Apples, Pears, Bananas

Please Login or Register  to view this content.

I will definitely try this!
I've recently discovered Power Query, it's really great.

Originally Posted by kakaNit

I will definitely try this!
I've recently discovered Power Query, it's really great.

I know that

so

---

if the problem is solved, hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)