Forum Discussion

Copper Contributor

Feb 12, 2019

Solved

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 stateme...

Example.xlsx10 KB

Formulas and Functions

Detlef_Lewin
Feb 13, 2019
I switched to an AGGREGATE() formula. See attached file.

techcommunity_335560_M25476_b.xlsx11 KB

Silver Contributor

Feb 12, 2019

Hi

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

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

techcommunity_335560_M25476.xlsx11 KB

GrantB
Copper Contributor
Feb 13, 2019
I have attached a file to describe what I previously posted
techcommunity_335560_M25476_Copy.xlsx11 KB
- Detlef_Lewin
  Silver Contributor
  Feb 13, 2019
  I switched to an AGGREGATE() formula. See attached file.
  
  techcommunity_335560_M25476_b.xlsx11 KB
  - GrantB
    Copper Contributor
    Feb 13, 2019
    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!!
GrantB
Copper Contributor
Feb 13, 2019
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.

Resources