Thread: Logical Formula to lookup value

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

2. 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. Thanks Zeddy

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

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

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

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

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

HowardC (2016-12-01)

11. 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. Hi Howard

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

zeddy

13. 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
•