Results 1 to 11 of 11
Thread: Logical Formula to lookup value

20161129, 22:50 #1
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Logical Formula to lookup value
I Have account numbers in Col A and text in Col E.
In Column M I have account number and in Col N, I want to lookup the value of the account number in Col M. If the account number in Col E contains "REV (INT)"r "from Opex", then the formula must return zero
I have need to formula to be amended to include "From Opex"
It would be appreciated if someone could kindly assist me and amending this

20161130, 05:36 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Howard
This formula would do it:
Code:=IF(VLOOKUP(M1,A$1:E$10000,5,0)="REV (INT)",0,IF(VLOOKUP(M1,A$1:E$10000,5,0)="from OPEX",0,VLOOKUP(M1,[BR1TB.xls]Data!$A1:$D10000,4,0)))
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
HowardC (20161130)

20161130, 09:11 #3
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Thanks Zeddy
I agree that it would be better to set up a helper column and use Index/match as less cumbersome

20161130, 23:04 #4
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Hi Zeddy
I have similar data, but in some instances #N/A is being returned
If "REV (INT)" or "from OPEX" does not relate to any of the account numbers in Col M, the value must be looked up in Col D in workbook BR1tb.xls
It would be appreciated if you would kindly amend my formulaLast edited by HowardC; 20161130 at 23:32.

20161201, 10:07 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Howard
see attached file for new formula.
Code:=IFERROR(IF(COUNTIF(M1,A$1:E$10000)=0,VLOOKUP(M1,[BR1TB.xls]Data!$A$1:D$10000,4,0),IF(VLOOKUP(M1,A$1:E$10000,5,0)="REV (INT)",0,IF(VLOOKUP(M1,A$1:E$10000,5,0)="from OPEX",0))),0)
I added a check column to show these (by comparing them with the values from your posted file)
The problem with the formula in your posted file is that if the first vlookup in the fomula doesn't find a match in column A, i.e. results in a #N/A for the lookup, then the IFERROR part of your formula is triggered, which then puts a zero value as requested by the IFERROR last condition.
So, to circumvent this, I first check to see if there is NO match, and if there is NO match, then look for a match in the other file. If there isn't a match in the [BR1TB.xls] file, then the IFERROR will put a zero value in etc etc etc
zeddy

20161201, 10:11 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Howard
..I suppose I should only count the entries in col A as I intended, so the formula should be:
Code:=IFERROR(IF(COUNTIF(M1,A$1:A$10000)=0,VLOOKUP(M1,[BR1TB.xls]Data!$A$1:D$10000,4,0),IF(VLOOKUP(M1,A$1:E$10000,5,0)="REV (INT)",0,IF(VLOOKUP(M1,A$1:E$10000,5,0)="from OPEX",0))),0)
zeddy

20161201, 12:19 #7
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Hi Zeddy
Thanks for the help and your inpout
What does =0 in the code meanCode:IF(COUNTIF(M1,A$1:A$10000)=0

20161201, 13:11 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Howard
..OOPs!
..that bit of the formula is supposed to be checking whether the code in column M can be found in column A, so it should be..
Code:IF(COUNTIF(A$1:A$10000,M1)=0
(so xxx=0 will give a True if the code in col M cannot be found in col A)
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
HowardC (20161201)

20161201, 13:17 #9
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
No Problem Zeddywe all slip up occasionally
I always appreciate your help as well as others on this forum
I have learnt a great deal over the years from this great forum

20161201, 14:05 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Howard
I had a think about it a bit more.
..I think this attached version should be the right one.
zeddy

20161201, 21:07 #11
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Thanks Zeddy It works perfectly