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?
Bookmarks