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

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?
RichShareAsked:
Who is Participating?
 
KnutsonBMConnect With a Mentor Commented:
i just put "" around the 1 in yoru vlookup formula and it worked perfectly

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


-Brandon
0
 
KnutsonBMCommented:
could you post a sample so we can test it?
0
 
RichShareAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SiddharthRoutCommented:
Try this

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

instead of

=VLOOKUP(1,Table_owssvr_1,2,FALSE)

Sid
0
 
SiddharthRoutCommented:
Thank you RichShare :)

Sid
0
 
KnutsonBMCommented:
hmmmmmm.........i think a point split would have been appropriate there...................

-Brandon
0
 
RichShareAuthor Commented:
Okay, new problem.  I now want to make the "1" a reference to another cell rather than a hard coded number.
0
 
KnutsonBMCommented:
can you post another question please?

Brandon
0
 
RichShareAuthor Commented:
OOPs!  Sorry Sid.  I will place a new question around the referenced cell if that's okay?
0
 
KnutsonBMCommented:
=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
 
SiddharthRoutCommented:
>>> OOPs!  Sorry Sid.

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

Sid
0
 
RichShareAuthor Commented:
Just returned #N/A on my file?  Am I doing something wrong (again)? :-)
0
 
SiddharthRoutCommented:
That's because the number is stored as text in the table. Try this

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

Sid
0
 
SiddharthRoutCommented:
Did it work?

Sid
0
 
RichShareAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.