Getting multiple values from a list for all items

[justme101]

Hello,

I have a list which has multiple line items for products which may repeat, and the next column has values which will change with each repetition as the fee changes.

Now, in the next sheet, there is a list which has no duplicates of the product names, but still all the different price changes for each product, like the 3 prices for product A, needs to be filled in the same row:

So, for e.g. product A will have its 3 different prices in the same row, but multiple columns can be used as it is empty after the column C. I did find a very complex formula for some website but pulled my hair trying to adjust it to my sheet. Any help here with a customized formula is greatly appreciated.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[justme101]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hey Fluff, just updated that. I am using Excel 365 on Windows 10. BTW, looking at your response has always instilled hope since I have been following this forum.

 

[Fluff]

Thanks for that.
How about

=TRANSPOSE(FILTER(Sheet1!$C$3:$C$16,Sheet1!$B$3:$B$16=C3))

 

[justme101]

Thanks for that.
How about

=TRANSPOSE(FILTER(Sheet1!$C$3:$C$16,Sheet1!$B$3:$B$16=C3))

Well, I'll be damned. This is smaller than the length of the actual product name I have. Thank you so much for this. So, I am guessing if the product names are in the columns, instead of rows, I just remove the TRANSPOSE and adjust accordingly?

 

[Fluff]

That should work.

 

[justme101]

That should work.

and 1 more thing, I checked this in office 2019, it didn't work. Is there any list which has the formulas which work and don't work between various versions?

 

[Fluff]

Have a look here Excel functions (alphabetical) - Microsoft Support
anything that doesn't have a version is 2007 or older