Excel export - problems with Table_owssvr and VLOOKUP

Question

I am having problems using the VLOOKUP function with a SharePoint Excel export file.

I have managed to get successful results for 2 of the lookups, but unless the columns in the owssvr sheet are side by side, and the first column is referencing the lookup cell, it doesn't work. For example:

The owssvr sheet has 4 columns. The reference cell is in column B [Project Title]. This formula works and returns the value for 'Project No.':

=VLOOKUP($C$5,Table_owssvr[[Project Title]:[Project No.]],2,FALSE)

... but this one doesn't:

=VLOOKUP($C$5,Table_owssvr[[Project Status]:[Project Title]],1,FALSE)

Do you know why this is happening and how I can resolve it? Also, do you know if it is possible to have more than 2 columns in the array?

Many thanks! Tina.

Answer 1

When you use VLOOKUP the lookup value HAS to be found in the most left column of the lookup array. Your description does not offer details about what is in the lookup array (an example) or what is in C5 but if C5 contains a Project Title, then only the first formula would work because the lookup array has Project Title in the left column.