Solved

Excel VLOOKUP won't recognise reference to an Excel table dervied from a sharepoint list

Posted on 2011-03-25
15
1,169 Views
Last Modified: 2012-05-11
My VLOOKUP request doesn't work because the table array is actually an Excel table derived from a sharepoint list.

=VLOOKUP(1,Table_owssvr_1,2,FALSE)

Any thoughts?
0
Comment
Question by:RichShare
  • 5
  • 5
  • 5
15 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214317
could you post a sample so we can test it?
0
 

Author Comment

by:RichShare
ID: 35214382
It is very basic as I am just proving the principle at this stage.

My objective is to create a printable report from a sharepoint list.  I have looked at various routes to doing this with the minimum number of steps for users.  I want users to start with a SP list and a simple print routine.  The closest I have got is to create an excel report in the reports centre, they put in the ID number for the list and it looks up the various fields and puts them in a nice report format.  But, the VLOOKUP doesn't seem to work.

Thanks for the help.
CAPA-Report.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214391
Try this

=VLOOKUP("1",Table_owssvr_1,2,FALSE)

instead of

=VLOOKUP(1,Table_owssvr_1,2,FALSE)

Sid
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Accepted Solution

by:
KnutsonBM earned 500 total points
ID: 35214394
i just put "" around the 1 in yoru vlookup formula and it worked perfectly

=vlookup("1",Table_owssvr_1,2,false)


-Brandon
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214462
Thank you RichShare :)

Sid
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214476
hmmmmmm.........i think a point split would have been appropriate there...................

-Brandon
0
 

Author Comment

by:RichShare
ID: 35214487
Okay, new problem.  I now want to make the "1" a reference to another cell rather than a hard coded number.
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214493
can you post another question please?

Brandon
0
 

Author Comment

by:RichShare
ID: 35214502
OOPs!  Sorry Sid.  I will place a new question around the referenced cell if that's okay?
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214503
=VLOOKUP(A1,Table_owssvr_1,2,FALSE)

Replace A1 with whatever cell you are trying to reference, i guess no need to post a new question
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214515
>>> OOPs!  Sorry Sid.

That's 'OK' since it is a genuine miss ;)

Sid
0
 

Author Comment

by:RichShare
ID: 35214532
Just returned #N/A on my file?  Am I doing something wrong (again)? :-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214545
That's because the number is stored as text in the table. Try this

=VLOOKUP(TEXT(A1,"@"),Table_owssvr_1,2,FALSE)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214709
Did it work?

Sid
0
 

Author Comment

by:RichShare
ID: 35230413
Hi Sid, just got round to checking it and it works fine.  Thank you for that and apologies again on the points distribution.  Have a good day. :-)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question