none
VLOOKUP formulas in Excel sheets on SharePoint do not work properly?

    Question

  • Hi all,

    A colleague of mine is trying to create VLOOKUP formulas in Excel sheets residing in a SharePoint server library.

    The syntax of such a VLOOKUP formula is:

    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    Normally (with files residing on a normal file share) after filling out the 1st parameter ("lookup_value"), the user can select a range of columns in another worksheet to indicate the "table_array" (2nd parameter). These selected columns are then displayed with dashed borders to indicate to the user that he has selected them. However, when trying to do this selection in Excel sheets residing in a SharePoint library, this functionality does not work. The columns are not shown in dashed borders and can also not be selected. As a consequence the table_array cannot be created. in the VLOOKUP formula.

    Does anybody know why this happens with Excel sheets on SharePoint?
    Is there a known workaround or configuration that needs to take place?

    Many thanks,

    Waldemar

    Wednesday, February 15, 2012 12:18 PM

All replies

  • Hi,

    There are some limitations of Excel files to be used in SharePoint.

    You cannot directly use VLOOKUP function like that.

    The alternative would be:

    1.Lookup column option

    There is an option in SharePoint list called Lookup column through which you can lookup data from other list.

    This will be definitely manual process.

    You can create a list using "Import Spreadsheet" , i.e. this will create List A

    Then create List B and in List B create a column with Lookup category which looks data from List A.

    2. Manually maintain the VLOOKUP range refer to the following :

    http://social.msdn.microsoft.com/Forums/sk/exceldev/thread/f94d25b6-749c-470c-837d-32bcf570bfd3

     

    Sincerely
    Rex Zhang


    Rex Zhang

    TechNet Community Support

    • Marked as answer by Rex ZhangModerator Wednesday, February 22, 2012 3:11 AM
    • Unmarked as answer by Vladja_ Friday, February 24, 2012 9:54 AM
    Monday, February 20, 2012 4:31 AM