Help with either VLOOKUP or IF AND statements

[GrantB]

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! 

[#2]

Hi

Neither VLOOKUP() nor IF() but INDEX() and MATCH().

Note: To make the formula work you have to remove some trailing spaces.

[#3]

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. 

[#4]

I have attached a file to describe what I previously posted 

[#5 · Accepted solution]

I switched to an AGGREGATE() formula. See attached file.

[#6]

You are the absolute BEST!!! 

You must be the guru on here! Thank you so much for your help.  You will save me so much time in the future.  Thanks again Detlef!! 

[#7]

You're welcome, Grant.