Forum Discussion

Jimbobmcwalton's avatar
Jimbobmcwalton
Brass Contributor
Feb 20, 2019

Is it possible to filter a lookup column using a specific criteria?

Hi There,    I am trying to get my sheet to return a result of 3 in a cell if 3 columns V9 W9 & X9 contain three specific words Im using =IF(AND(V9="WORDONE",W9="WORDTWO",X9="WORDTHREE"),3,1) and i...
excel
Formulas and Functions
bskipper's avatar
bskipper
Copper Contributor
Feb 20, 2019

Change you AND statement to OR, which means if any of these are true select 3.  The AND statements states if all are true use 3, which will not happen.

  • Jimbobmcwalton's avatar
    Jimbobmcwalton
    Brass Contributor
    Feb 21, 2019

    Hi, thanks for the reply.  I can see how this works but it doesnt give the result I need.

     

    The three words actually change as they are attributed to the same account (wordone) but the account type (wordtwo) and salesperson (wordthree) all change.  This is brought onto the main sheet from the tab Account which also changes as wordthree shows whether an account is over a year old or not (which then changes from TRUE or FALSE)

     

    I have a Table Called ProbMatrix) which is refered to by using INDEX and this returns the correct value fine. (the % commission) my other formulas then use this to calculate the figure i need to show commision amount. 

     

    =INDEX(ProbMatrix[[#All],[Commission Rate]],MATCH(1,(V3=ProbMatrix[[#All],[Account]])*(W3=ProbMatrix[[#All],[Sales Person]])*(X3=ProbMatrix[[#All],[Over a Year]]),0))

     

    I am then trying to get certain account types to have a fixed amount of £3 and ignore the result from the above formula which ranges from either 2.5% or 10% commission.

     

    Not sure if you know a way to do this, as the column is formatted to show % so the £ amount in the table is being transfered to %.  

     

     

     

     

     

     

     

     

     

Resources