Hi
I need some help please.
The below formula used to work until I added a formula into cell B9 and now I get the equivalent of the #NA message when cellB9 is blank. It works perfectly on non blank cells
=IFERROR(IF(ISTEXT(B9),VLOOKUP(B9,'Project Codes'!$A$4:$B$22,2,FALSE),IF(B9=0,VLOOKUP(G9,'Nominal Codes'!$A$2:$B$68,2,FALSE))),"NO")
The formula I have used in B9 is =IF(LEFT(G10,2)="WA",LEFT(G10,6),"")
I need both formulas to work.
Can anyone help me please?
When the formula in B9 = "" then ISTEXT(B9) = TRUE and executes the 1st VLOOKUP.
Maybe replace ISTEXT(B9) with:
COUNTIF(B9,"?*")
That's almost the same as ISTEXT but it will ignore the formula blank "".
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Can you attach the book?
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
can you post the sheet? It's much easier to work of a real sheet...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
If B9 is
=IF(LEFT(G10,2)="WA",LEFT(G10,6),"")
Then
ISTEXT(B9) will ALWAYS be TRUE.
Because the LEFT function returns a Text string, even if it looks like a number.
So B9 can only return "" <- A text string, or LEFT(G10,6) <- Also a Text string
Perhaps
=IF(B9<>"",VLOOKUP(......
There's also a next level lookup to account for:Originally Posted by Jonmo1
...,IF(B9=0,VLOOKUP
If the cell contains 0 then B9<>"" is TRUE and it will execute the wrong lookup.
I've mocked this up on a single sheet. Under what circumstances can you get a zero in B9???
B9 will never = 0 either.
I think the whole original formula needs to be reconstructed.
What results do you want (OP) based on what possible results of the formula in B9 ?
expenses proforma.xlsx
Hi Hopefully I have attached the sheet
Hopefully the sheet above will make it clear. Column b is looking for the first 6 letters in column g but only if they start WA, otherwise I want the cell to remain blank.
Column F is looking up the nominal code (accounts) from two separate lists dependant on whether there is an entry in column B
There that sounds so simple doesn't it!
Try this in F8 on the Expenses sheet.
=IFERROR(IFERROR(VLOOKUP(B8,'Project Codes'!$A$4:$B$22,2,FALSE),VLOOKUP(G8,'Nominal Codes'!$A$2:$B$68,2,FALSE)),"No")
Jonmo1 you're a genius :D
That works Perfectly
You're welcome.
How do I mark this as solved
Select Thread Tools from the menu link above and mark this thread as SOLVED.
Yeah, I should have caught that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks