Forum Discussion
Is it possible to filter a lookup column using a specific criteria?
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.
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 :)
