Jan 20 2023 06:20 AM
Jan 20 2023 06:20 AM
I have been trying to find a formula but so far it was only try and error.
Basically, the idea is simple: I have different products (1-20) in row 1 and those might have, if the participant had indicated it, a calculated price in row 8 of each matrix. In total I have the same table five times.
Now, I would like to display all the indicated prices sorted from lowest to highest for each product and from all matrix. My difficulty was to indicate the formula that it should only tell the number, text, etc in row 8 when it is matching the product and then additionally when I tried to use an array to not only display the same but skip to the next column with the same product and display the price and so on...
I had been trying index, hlookup, if and those in several combination but the only thing I was able to achieve was displaying all prices or prices from a certain cell and finally the #ref! error appeared.
Thanks for your help!
Here is an extract from my sheet.
Jan 20 2023 09:10 AM
It's not a well designed table or database, which is the root of your difficulties. INDEX, MATCH and HLOOKUP (and all the other LOOKUP functions)...they all rely on a well designed table.
Before we try to find something that will work with the data as you currently have it arrayed, are you in a position to radically modify the way you organize the information?
Here is a link to a whole set of YouTube videos on Tables in Excel. Look at one or two of them (maybe more) to get the hang of how a table should be designed. Then by all means come back and ask questions.
Jan 20 2023 12:08 PM
Jan 20 2023 01:13 PMSolution
I'm sorry if my response seemed "mean," for it certainly wasn't my intent nor my mindset at all when I was writing; the reason I gave you those links to YouTube was that it's often easier for people to learn from those videos than from a verbal description alone. I think you put your finger on what's confusing in your design in your first post, when you said, and I'm quoting, "In total I have the same table five times." But that's also confusing because your image shows eight, not five, similar arrays of information.
I will also attach a basic table in which I'll reorganize some of the data from your image to start populating it.
The first concept to implement, though, is to
That said, there may be other design issues, depending on what you're trying to do WITH the table once it's a single table. You will find that VLOOKUP (rather than HLOOKUP) can work, because it can go through the entire table looking for a match. Same with INDEX and MATCH. However, they all tend to work better when there's at least one column where each entry is unique. (Having, for example, several rows where Product Name is the same, gets in the way.)
But all of these other fine points of table design really depend on what you want to accomplish with your table. And that's why I referred you to those YouTube links. So please, take a look at the attached start--that's all it is--at a table, but also go look at those YouTube links to determine more fully what it is that a table can do for you in your setting, how it could be improved in design. And then come back and ask questions.