+ Reply to Thread
Results 1 to 3 of 3

Modified LOOKUP Formula for Excluding Missing Lookup Values

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    North Dakota
    MS-Off Ver
    Excel 2010
    Posts
    8

    Modified LOOKUP Formula for Excluding Missing Lookup Values

    I have an example spreadsheet, but neither file or image attachment seems to be working (maybe someone knows a tip or trick for me, but neither is even giving me the option to select an attachment). In any case, maybe I've explained the problem effectively enough that it will make sense without the visual.

    I am using the LOOKUP function to attempt to combine two tables into one table. Both tables (Tables 1 and 2) have the same IDs in one column with the exception of several IDs that do not exist in one table or another.

    The desired combined table (Table 3) should have one column for the IDs and two columns for values; one column for values from Table 1 and one column for values from Table 2.

    Currently the LOOKUP function is allowing me to add a second value column to Table 1 containing the values from Table 2. However, when (for example) an ID present in Table 1 does NOT exist in Table 2, the LOOKUP function apparently chooses the value of the preceding ID for the ID that is missing in Table 2. It seems that because the ID is not present in Table 2, the LOOKUP function (or a more appropriate formula/function) should instead yield a "0," "error," or something similar in the column for values pulled from Table 2.

    I am looking for the simplest way to either combine Tables 1 and 2 into a Table 3 in which either (1) all IDs are present and a '0,' 'error,' etc. is entered into the appropriate value column, or (2) the rows for IDs that are not present in both Tables 1 and 2 are completely absent from Table 3.

    I hope that my question makes sense, please let me know if I can clarify anything.

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Modified LOOKUP Formula for Excluding Missing Lookup Values

    The VLOOKUP function has an argument at the end to specify whether to find an exact match, and what is to occur if no exact match is found. Make sure the last argument is FALSE or 0 and it will return an error if it cannot find an exact match.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  3. #3
    Registered User
    Join Date
    08-31-2012
    Location
    North Dakota
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Modified LOOKUP Formula for Excluding Missing Lookup Values

    gak67, perfect! That was far easier than I anticipated.

    Much thanks for your help!

+ 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. [SOLVED] INDEX-MATCH: Dealing with missing lookup values
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-19-2017, 02:08 AM
  2. [SOLVED] Modified Lookup
    By drgkt in forum Excel General
    Replies: 14
    Last Post: 12-10-2015, 04:01 PM
  3. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  4. [SOLVED] Modified Lookup value
    By stegngas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2014, 02:57 PM
  5. Problem with lookup formula - missing values
    By Geomarsh in forum Excel General
    Replies: 9
    Last Post: 08-12-2009, 11:29 AM
  6. Lookup Formula with two lookup values
    By randolphoralph in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-18-2008, 01:36 PM
  7. Replies: 3
    Last Post: 10-10-2005, 01:05 PM

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