Hey,
(1)How do you stop error messages and return a blank when an error is found on a lookup.
(2) How does it work if you you also have to trim the lookup value and table array and also deal with complication such as acell treating as text and another a value etc. What formula do you use.
(3) What do the various error messages mean i.e 0 etc?
Want to make logical sense of these error messages once and for all as they may add value.
Thankyou,
=IFERROR(VLOOKUP(A1,Sheet2!A:C,3,0),"")
the empty double quotes returns your blank or anything else you want to put in there....HTH
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
Thanks, Is iferror a real excel function or typo because i have never heard it before?
How about questions 2 and 3 , many thanks?
good point.....if you are using Excel 2007 or greater IFERROR is a valid function....
2) I just put a question to the forum about this a couple a days ago....to TRIM the table array you would do this
=IFERROR(VLOOKUP(A1,TRIM(Sheet2!A:C),3,0),"") and then do cntrl-Shift-Enter...IE: an array formula....
3) the list for errors is too large to put here....but generally #N/A means "It didn't match anything"...
Thank you learn something luckily i am using excel 2010.Guess is error is now defunct and replaced by if error.
(1)What if you also need to trim A1? What s the correct formula then?
(2)Is this essentially the perfect lookup if you want to return a blank when there is and error and not worry about triming false readings etc?
(3) why you have to use array formula ? whats logic?
=IFERROR(VLOOKUP(A1,TRIM(Sheet2!A:C),3,0),"") and then do cntrl-Shift-Enter...IE: an array formula....
Cheers
=IFERROR(VLOOKUP(TRIM(A1),TRIM(Sheet2!A:C),3,0),"") then doing the array formula would be very close to the perfect vlookup for cleaning up errant spaces....
the logic behind the array formula is that Excel converts the formula data to what might be considered a table and then applies the TRIM statement (which can only be applied to a single cell and only works on strings/text type data)....the only bad thing about this big formula is if you have alot of data, applying might take some time....
thank you do you have a vba 2010 code that asks user to enter value and table lookup and does it all quicker and returns the values the selected columns or cell and fill down?. Maybe even a customised macro toolbar button flexible in use?
I guess this would be the ultimate flexible lookup?
cheers
nope....most people use the formula assistance when they go that direction....I tend to type =IFER and then tab because the system automatically chooses the rest of the IFERROR and same with VLOOKUP....it really speeds things up....
On side note.....forum ettiquette recommends changing the status to SOLVED by using the thread tools and giving a star to folks who give you a hand....and when you pass on your expertise on VLOOKUPS someone will give you a star....
i am to be etiquette i am newbie.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks