+ Reply to Thread
Results 1 to 12 of 12

VLOOKUP with robust Error checking - Help!

  1. #1
    Registered User
    Join Date
    07-17-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    6

    Question VLOOKUP with robust Error checking - Help!

    Hello all,

    I've decided to ask since I couldn't find similar post about my issue. Would appreciate any help here.

    Tasks:
    Imagine that I have a spreadsheet w/ a list of products (pie's) and each pie has a batch # and is also categorized into A, B, C ...
    As you can see from the attached spreadsheet, sometimes, the same product is not categorized.

    I'd like to run a VLOOKUP on this sheet looking for the batch # in column 1, if there's a match then I'd like to return the "category" in column 2.
    Take Blueberry pie, e.g. (in "Sheet1"), the first time VLOOKUP encounters this product in cell C3, the category is empty so I'd like VLOOKUP to return 0 or something similar AND continue searching the list (it should not stop there because there's another instance of Blueberry pie in cell C10. If the product is not on the list, then return N/A or something similar.

    My approach appears to works so far (see "Sheet2") but I'd like to make the formula more robust by incorporating functions such as: ISBLANK, IFERROR, etc to make it bulletproof - so it catches every possible error or situation. Obviously, I've simplified the data provided but imagine if there were 5,000 different products on the list, e.g.

    Does anyone have ideas on how to do this?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VLOOKUP with robust Error checking - Help!

    Maybe with a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: VLOOKUP with robust Error checking - Help!

    Hi

    array formula
    =INDEX(Sheet1!$B$2:$B$14,MATCH(1,IF(ISERROR(MATCH(Sheet1!$A$2:$A$14,A2,0)),0,1)*ISTEXT(Sheet1!$B$2:$B$14),0))

  4. #4
    Registered User
    Join Date
    07-17-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    6

    Re: VLOOKUP with robust Error checking - Help!

    @Sadath31 - I'm a bit confused. As you know all the data, in reality, will not be in a single sheet. In your formula, in the case where you have two sheets (one with the parameters, and the other for the results), where is Sheet1 and Sheet2?

  5. #5
    Registered User
    Join Date
    07-17-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    6

    Re: VLOOKUP with robust Error checking - Help!

    @oeldere - thanks for the response but a pivot will not work for what I'm trying to do. i appreciate the help, however.

  6. #6
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: VLOOKUP with robust Error checking - Help!

    Hi

    The formula given is for Sheet2, your present Vlookup formula to replace with the formula i given. That's what you need, right?

    Copy the formula and paste (in formula bar) cell B2 in sheet2 & Hold Ctrl, Shift then hit Enter.

    you can change the range as per your requirement..

    Thanks

  7. #7
    Registered User
    Join Date
    05-04-2017
    Location
    MALAYSIA
    MS-Off Ver
    2007
    Posts
    34

    Re: VLOOKUP with robust Error checking - Help!

    I will try to help you later when i am free

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: VLOOKUP with robust Error checking - Help!

    pricetag welcome to the forum.

    Are you OK with sorting the 'Sheet1' with "Batch #" as the sort key or even doing a custom sort?
    Are you OK with helper columns in 'Sheet1'?
    Dave

  9. #9
    Registered User
    Join Date
    07-17-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    6

    Re: VLOOKUP with robust Error checking - Help!

    All,

    Thanks for the responses but when I tried it on my actual (more complex) dataset, it did not work. Let me try and explain it again.

    In the attached file, there are two tabs (in my actually dataset, these are in two separate spreadsheets -- combining it here for simplicity)

    "data" -- items with assigned category
    "result" -- where I want to do the actually VLOOKUP. not everything on this list has been categorized in "data"

    Objective:
    1) In C2 of "result", do VLOOKUP on cell A2 in "data", if found, put correct value of "data"/C2 into "result"/C2. Repeat for remaining items on "result"/column 1
    2) Do exhaustive error checking in "result"/column 3 to make sure everything has been checked/read and nothing was missed .

    Does this help? Thanks again!
    Attached Files Attached Files

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: VLOOKUP with robust Error checking - Help!

    My proposal:
    1. Sorts and groups 'data' using Batch # as the sort key (ascending).
    2. Two helper columns that return the lower and upper row boundaries of the grouped Batch #s.

    The formulas for the helper columns are:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in C2 of 'result' this formula filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-17-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    6

    Re: VLOOKUP with robust Error checking - Help!

    @FlameRetired - thanks for the response! I'm getting an error in the 2nd helper formula.
    In your formula, COUNTIF($A:$A ...

    What cell is $A:$A referencing? It' giving me an error. I really wish there was a simpler way of doing this - get's really cumbersome when working on big/real data set.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: VLOOKUP with robust Error checking - Help!

    $A:$A is referencing the whole column $A:$A.

    Did you sort the source data first? There should be no errors unless the first helper formula returns an error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-04-2016, 05:11 PM
  2. [SOLVED] Add error checking to existing VLOOKUP formulas
    By maw230 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2015, 02:06 PM
  3. [SOLVED] Error checking routine - check range values on various worksheets and report if error
    By TheRobsterUK in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-29-2014, 06:36 AM
  4. Replies: 2
    Last Post: 02-18-2013, 10:17 AM
  5. [SOLVED] Error checking
    By kello99 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2012, 09:34 AM
  6. Error checking
    By Cbrehm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2010, 07:25 PM
  7. error checking
    By ghadir14 in forum Excel General
    Replies: 6
    Last Post: 10-15-2007, 06:44 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1