Forum Discussion
Help with either VLOOKUP or IF AND statements
Hello,
I have a large table (~3000+) rows where we have multiple samples and each row has a different information.
My goal in this is to create either a =IF AND statement or VLOOKUP statement, so that in another sheet all I have to do is enter the name Apple and it populates the columns with the data requested automatically, pulling from that large table of data. I want to do this without manually copying and pasting "transpose" data. This way, I can continue to paste onto this large table as I get more data, and on the separate sheet it will populate the data and all I have to do is simply type in the unique sample name. I have an example attached.
Right now I've tried both IF/AND and VLOOKUP statements, and every way I have tried it is not popping out the value once for the same row once both AND statements match. Sometimes it pops out the value from a column where one AND statement matches but not the other. It is hard to figure out. I have an example of an equation on my sheet attached, but it is leading to #N/A at the moment.
If anyone can help me it would be greatly appreciated!
I switched to an AGGREGATE() formula. See attached file.
6 Replies
Hi
Neither VLOOKUP() nor IF() but INDEX() and MATCH().
Note: To make the formula work you have to remove some trailing spaces.
I have attached a file to describe what I previously posted
I switched to an AGGREGATE() formula. See attached file.
Hello Detlef,
I think we are on the right path here. The other thing is however, that for some samples, it does not follow the exact formula of "Color - Taste - Smell - Appearance - Shape". For instance, some of my sample names might just have "Color - Taste" or it might be out of order such as "Taste - Color - Smell".
That's why I was trying to look up for two matching values in case they happen to be out of order. I like the match function because that seems to be exactly what I'm looking for! However for the columns labeled 0-4, these values will get mixed up if the "analysis" deviates from what I showed. I should have specified that in the original post! That is my bad.
