finding reference cells from a range

Hi! Let's assume that the L1:BF1 columns include the name of the supplier. Under these columns (L5:BF281), there is price information for each raw material (raw materials' names are on B5:B281). As you imagine, each raw material is not bought from each supplier. For each row (raw materials) I have just 1, 2, or maximum of 3 columns that include the number (price).

I want to have one cell that gives me information like that (price1,supplier1;price2,supplier2) for raw materials. For it. I need to check the rows includes the price for each raw material and according to that information I need to return supplier value.
How can I do it?

Welcome to the forum.
I assume you want to list the results in column K, so try this array formula in K5.
Paste it and confirm with Ctrl+Shift+Enter instead of just Enter. Then copy down.

Formula:

Please Login or Register  to view this content.

My Excel version at work does not have the TEXTJOIN function, but I guess yours does, so you can use it not to show commas when there's only one supplier.
I did it to two suppliers, but you can add a third one by copying the last two INDEX formulas and changing the last argument (2) to 3.

Good luck!

Thank you for your advice. I already tried to adopt the formula to my settings and now I am getting an error like "you entered too few arguments for this function" and after this error excel directly forward me to between the last two parenthesizes, so probably it needs the formula or value for "value_if_error" case.