LinkBack URL
About LinkBacks
Can some one explain this formula to me in plain english? I don't understand why the ISERROR function is being used, or why VLOOKUP is in there twice.
Formula: =IF(ISERROR(VLOOKUP(J6,V:W,2,FALSE)),"",VLOOKUP(J6,V:W,2,FALSE))
Thanks!
IF, ISERROR and double VLOOKUP
Can some one explain this formula to me in plain english? I don't understand why the ISERROR function is being used, or why VLOOKUP is in there twice.
Formula: =IF(ISERROR(VLOOKUP(J6,V:W,2,FALSE)),"",VLOOKUP(J6,V:W,2,FALSE))
Thanks!
Last edited by myshadeofglory; 10-21-2011 at 09:14 AM. Reason: Added formula
You have not mentioned where....i dont see any iserror or double vlookup. Please explain or attach a file.
I'm taking a guess here as you haven't posted your formula, but it sounds like you're describing the 2003 way of removing the error if there is no match. The first part of the formula tests to see if the v lookupis an error, if it is it displays nothing, if not it returns the valie of the second vlookup
That's quite likely, Kyle123. You said 2003 though. Does 2007 offer a different solution?
I believe there are some If Error tests in 07 and later that make this obsolete. I don't have any of these versions though but I'm someone can clarify
Yes, instead of the duplication of the formula, in 2007 onwards you can simply use:
=IFERROR(VLOOKUP(J6,V:W,2,FALSE),"")
However, be aware that these formulas aren't backwards compatible, so you may have problems if anyone ever needed to use/view the spreadsheet on an earlier version.
In 2007, you can use IFERROR and process the VLOOKUP once only..
e.g
=IFERROR(VLOOKUP(J6,V:W,2,FALSE),"")
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
The VLOOKUP formula is in twice because the first use determines if it returns an error (normally a #N/A error with VLOOKUP). If it is then the ISERROR allows a controlled response to be given.
The second VLOOKUP (once it has been confirmed that it's not going to fail) actually returns the required value.
In Excel 2007 you can shorten the formula to =IFERROR(VLOOKUP(J6,V:W,2,FALSE),"")
Wow, second time today - it's like we're working in triplicate today guys!
I just follow NBVC round, paraphrasing all of his posts, in the hope that newbies think that I know what I'm talking aboutOriginally Posted by brokenbiscuits
I tried the same formula above in 2007 and it still works.
There's no problem using that formula in 2007, it's just neater and (slightly) quicker to use the IFERROR function.
hehehe if only you could make his formulas shorter too (into something I can follow), then I'd be really impressed
=IFERROR(VLOOKUP(J6,V:W,2,0),"")
simples....
Audere est facere
Oooh I see, it makes so much sense now, don't know why I didn't see it before!!
Thanks dll
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks