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

Brass Contributor

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 im getting a result of 1 even though it should be 3 - would anyone know whats wrong with this formula?  I have checked for eronious spacing in the source table so its not that.

 

The three words are being returned into the 3 columns are as a result of using a VLOOKUP.

 

 

Any ideas ?

 

Cheers in advance 

 

Jim 

 

 

4 Replies

Hi @Jimbobmcwalton, I tried your formula exactly as written with the word lookups as wordone, wordtwo and wordthree. I tried all caps and got 3. I tried all lower case and got 3. I tried using = the cell above and got 3 and I tried VLOOKUP and initially got 1.

 

The only thing I was able to find that changed my VLOOKUP result to 3 was adding "false" as the range lookup in my VLOOKUP formula. Once I added false, I got the result I was looking for. If you did not include false as the range lookup in your VLOOKUP formula, I believe adding it should make your formula work as expected.

2-20-2019 10-35-24 AM.jpg

 

2-20-2019 10-38-11 AM.jpg

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.

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

 

 

 

 

 

 

 

 

 

Hi, thanks for the reply.  Yes i do use TRUE or FALSE.

 

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(WORDONE)to have a fixed amount of £3 if under a year old, back to 2.5% if older than a year 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 %.  

 

Its such a simple thing I need to do I am wondering if I am just making it too complicated :)