Forum Discussion
Give price for product based on chosen product
Hey Community!
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.
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
- take your row headings and make them column headings, and then just have one row per response. (the way yours currently is organized, you are, in effect, creating multiple tables rather than just one. Doing that--multiple tables--will greatly interfere with those lookup and index functions that you were trying out)
- then you'll have to modify some of your data to accomplish this: be consistent with the content in any one column. You switch back and forth between the unit being kg and bag, sometimes specifying the size of the bag in kg terms, and then the various Quantity columns themselves go back and forth between bag and kg. Granted, that may be the way your survey results came back, but for a table to be useful, you will want consistency for any given aspect of the product (i.e., any given column)
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.
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.
- First of all, thanks for your response.
Since I have just started using excel for my first survey: I am trying. Of course if I had had more time then I would've been prepared. However, I had not had the time and, unfortunately, no one in my surrounding has sufficient experiences with excel which is why I ended up here today so that I can not only understand where my mistakes are but also on how to solve them quickly so that I can learn from you to become better and move on.
So please be less mean to beginners as I am one. (The last sentence wasn't necessary.). Everyone had been there at some point. In the end we all have the same target, haven't we?
So if you can give me a proper feedback please, why in your eyes my table is not well designed, I can have a look at it more precisely and I know on what exactly I have to work. If not, well thanks at least for your first respond.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
- take your row headings and make them column headings, and then just have one row per response. (the way yours currently is organized, you are, in effect, creating multiple tables rather than just one. Doing that--multiple tables--will greatly interfere with those lookup and index functions that you were trying out)
- then you'll have to modify some of your data to accomplish this: be consistent with the content in any one column. You switch back and forth between the unit being kg and bag, sometimes specifying the size of the bag in kg terms, and then the various Quantity columns themselves go back and forth between bag and kg. Granted, that may be the way your survey results came back, but for a table to be useful, you will want consistency for any given aspect of the product (i.e., any given column)
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.
