Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 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)))
    ..but I would use Index/Match with a helper column rather than lots of VLOOKUPs

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2016-11-30)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Zeddy

    I agree that it would be better to set up a helper column and use Index/match as less cumbersome

  5. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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 formula
    Attached Files Attached Files
    Last edited by HowardC; 2016-11-30 at 23:32.

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 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)
    There were some other cells that should have returned non-zero values.
    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
    Attached Files Attached Files

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 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)
    ..of course, there are other formulas to do this as well!

    zeddy

  8. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help and your inpout

    What does =0 in the code mean
    Code:
    IF(COUNTIF(M1,A$1:A$10000)=0

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 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
    ..Please forgive me!

    (so xxx=0 will give a True if the code in col M cannot be found in col A)
    zeddy

  10. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2016-12-01)

  11. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    No Problem Zeddy-we 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

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    I had a think about it a bit more.
    ..I think this attached version should be the right one.

    zeddy
    Attached Files Attached Files

  13. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Zeddy -It works perfectly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •