+ Reply to Thread
Results 1 to 3 of 3

Lookup Formula with the search range is the result of another formula in tables

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    2

    Lookup Formula with the search range is the result of another formula in tables

    Hi,

    I have been trying to use VLOOKUP with this problem and not having any luck. I have two worksheets in a book of which both are formatted as tables. I am able to refresh the table to pull additional new data from the internal network on a regular basis. Thus, I need to keep the heart of the table as is. I add additional columns to the right in order to have specific information for pivot tables and pivot charts. In the attached example, the column headings in blue are the original table columns while those in red are those that I have added. The first tab "Performed" information contains the year of which the row data was created and has the reference document but with the full name of the document.. Unfortunately, the tab "Actions" does not have the year information and instead of having the document full name, it lists only the document number "FSNo". Thus, I first column I added to the tab "Performed" was FSNo and added the formula to subtract ".xml" from the file name ( =LEFT([@Name],LEN([@Name])-4) ), that was easy. Also, as I am accustomed to vlookup only going to the right, I added another column "Year2" with the formula ( =B2 ), again ... easy. So, in tab "Actions", I only have two columns in this example; the original column "FSNo" and the added column "Year" of which I input the formula ( =VLOOKUP([@FSNo], Table_Performed[[FSNo]:[Year2]],2,FALSE) ), the result is the dreaded #N/A. I have confirmed that both columns "FSNo" are number format of which I do not desire to change the formatting of the "FSNo" column within the "Actions" tab.

    Any ideas?
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,457

    Re: Lookup Formula with the search range is the result of another formula in tables

    Because of the two FSNo is different: one is text and other is value. Try to convert into number by add 0.
    BTW, no need additional column to use VLOOKUP, by using LOOKUP like this:

    =LOOKUP(2,1/(Table_Performed[FSNo]+0=[@FSNo]),Table_Performed[Year])

  3. #3
    Registered User
    Join Date
    12-10-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    2

    Re: Lookup Formula with the search range is the result of another formula in tables

    That did the trick. I thank you kindly.

+ 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] Match formula to search range of columns to return True or False result
    By Twaddy006 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2015, 05:42 PM
  2. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM
  3. [SOLVED] Lookup formula required to search for two criteria and return result
    By dave1983 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-12-2012, 03:29 PM
  4. [SOLVED] Help with formula to search 2 cells and produce a result
    By champion777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2012, 07:11 PM
  5. Replies: 4
    Last Post: 05-22-2009, 11:17 AM
  6. Match/Lookup/Result Formula
    By Killer17 in forum Excel General
    Replies: 1
    Last Post: 11-03-2008, 10:28 PM
  7. Lookup formula with multiple tables or range names
    By phatmik3 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2007, 08:35 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