I have a file with multiple worksheets and I need to do vlookup of UPCs from 1 worksheet called 0723 and if it is null value then vlookup from another worksheet called productmaster. The formula I came up with is
=IFERROR(VLOOKUP(UPCLookup!A2,'0723'!$B$2:$Y$63014,23,FALSE),IFERROR(VLOOKUP(UPCLookup!ProductMaster!B2:S149824,18,false),)))
This returns an unspecified error and higlights the B2 after productmaster!
What is wrong with my formula?
Please help I am pulling my hair out.
Last edited by i.beard; 02-12-2009 at 04:40 PM.
There's some big ranges there... but perhaps:
=IFERROR(VLOOKUP(UPCLookup!A2,'0723'!$B$2:$Y$63014,23,FALSE),IFERROR(VLOOKUP(ProductMaster!B2:S149824,18,false),))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
That gives me a #Name? as a result. This is the same problem I have been running into. Yes the ranges are huge. Somehow 6 weeks of work got overwritten (programmer issue) so I am trying to consolidate old work with new work so that I do not have to spend another 6 weeks working on it.
I cannot figure out why I am getting that result. If you have any more suggestions I would be eternally grateful.
What does
=IFERROR(1/0,"a")
return ?
You definitely running 2007 ?
=IFERROR(1/0,"a")
Returns A
I tried a different route shown below and got a circular error.
=IFERROR(VLOOKUP(A2,UPCLOOKUP,FALSE),IFERROR(VLOOKUP(B2:Y63014,23,FALSE),IFERROR(VLOOKUP(ProductMaster!B2:S149824,18,FALSE),)))
Definately running 2007
My last one returns NAME because no lookup criteria is set for the 2nd VLOOKUP... I based mine off your original but didn't check it, apologies... your first one you see I think has a cell reference missing, see:
I took out the UPCLookup! as I thought this was an error and didn't think to check you had a lookup criteria, so in hindsight I suspect it should be:VLOOKUP(UPCLookup!ProductMaster!B2:S149824,18,false)
The above could be wrong in terms of the lookup criteria but that is the source of your problem.VLOOKUP(UPCLookup!XY,ProductMaster!B2:S149824,18,false)
Corrected to the following
=IFERROR(VLOOKUP(UPCLookup!A2,'0723'!$B$2:$Y$63014,23,FALSE),IFERROR(VLOOKUP(UPCLookup!A2,ProductMaster!B2:S149824,18, FALSE),))
Now it opens an update values dialog box and wants me to select a file to update from...?
Okay now corrected to:
=IFERROR(VLOOKUP(UPCLookup!A2,'0723'!$B$2:$Y$63014,23,FALSE),IFERROR(VLOOKUP(UPCLookup!A2,ProductMaster!B2:S149824,18,FALSE),))
and it is returning 0
Which is odd because if it is null in both which this one happens to be shouldn't it return " " ?
Check the spelling of your sheet names... the dialog is appearing because the formula can not find the references within the current workbook.
EDIT: no longer relevant.
A Null is not an error value... if the VLOOKUP returns a Null or 0 that will not invoke the IFERROR... only an actual Error will do that -- the most common in relation to a VLOOKUP would be #N/A
Sorry I think I worded that wrong, if the cell is blank shouldn't it based on my formula return a " " ?
No, as I say IFERROR is an IF ERROR ... a blank/null is not an error... an error is an #N/A; #VALUE!; #DIV/0! etc...
Using your example the IFERROR will only invoke if either of the following holds true:
a) the look up value can't be found in the lookup range --> ie generates an #N/A
b) the resulting value from the lookup (ie from the column being returned) is an actual Error ... ie the cell found contains #VALUE! etc...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks