CAML – Query Lookup Field by ID; not by Value

When lookup fields are created, sharepoint stores them as
ID;#Value format in related list.

for e.g.
in list “Contacts” there is column called “Country”.
There is item in contact with value “India” assigned in Country, having item ID=10.

Now for another list “List2” when column “Country” is stored as Lookup column named “refCountry”; and for certain data item “India” is selected from combo box, Sharepoint stores it in,

10;#India in  “List 2” -> “refCountry” column.

When having CAML query in List2

The typical query looks like
<FieldRef Name=”RefCountry”  />
<Value Type=”Lookup”>India</Value>

The disadvantage of this way is If  list Contacts is having more than one entry(item or row) having value India for Country it will return the only first one. This may give inconsistent data for further.

To avoid this query should be base on ID not by Value.
This can be achieved thru :

<FieldRef Name=”RefCountry” LookupId=”TRUE” />
<FieldRef Name=”RefCountry” LookupId=”TRUE” />
<Value Type=”Lookup”>10</Value>


where 10 is the ID for item having country=”India”


  1. May 9, 2008 at 8:28 PM

    […] Lists – Views Bug ? in earlier blog post “caml query: lookup field by id not by value” i have mentioned how you can query lists for lookup field based on lookup field […]

  2. July 3, 2008 at 12:16 PM

    Thanks for this great tip. I was just solving the exact issue of searching list by lookup id, and fortunatelly I find your site.

  3. Marc Bayé Saltó said,

    September 23, 2008 at 9:17 AM

    Be carefully using the LookupId=”TRUE”, it seems that doesn’t work with lookups over the 16th.
    If you’ve got a list with more than 16 lookups and you need a query over the 17th, you’ll obtain results matching another lookup (if you didn’t deleted any lookup on that list, the results will match the first one, if you query the 18th, results will be on the second one, and so one…)
    There’s a post on the Technet about this possible bug.

  4. PeterB said,

    January 13, 2009 at 12:05 AM


  5. priya said,

    March 20, 2009 at 6:24 AM

    it looks great

  6. priya said,

    March 20, 2009 at 6:25 AM

    this was an great article

    • Anonymous said,

      June 13, 2013 at 9:25 AM

      Yes… it is!

  7. hussain naqvi said,

    September 10, 2009 at 7:34 PM

    Is there any way to get the data without having “ID;#”? I want to get only Value from that dropdown column.
    Because I am binding returned data set to my gridview without doing any changes in dataset. So my grid is showing value of that column as ID;#Value.

    • October 13, 2011 at 4:29 PM

      If u knw hw to remove ID;# before a column value,plz let me knw.Its very urgent for me.I have created a customer name Lookup column.I displaying the values like 1;#Honey

    • shirisha said,

      October 26, 2011 at 12:03 AM

      to remove ;# from ID;#Name for a lookup field column value, use XSLT code function substring-after(@fieldname,’;#’) in designer.
      for eg:substring-after(@Customername,’;#’) to get only customername as a field value(say shirisha rather than 1;#shirisha)

  8. ashiena said,

    December 7, 2009 at 8:20 AM


    how do we find the value of 10 ?

  9. Steve Bloomer said,

    March 2, 2010 at 11:40 PM

    Perfect answer to the question I was asking on Google


  10. Gary said,

    May 19, 2010 at 5:32 AM

    Thank you for posting this. It was incredibly helpful at just the right moment.

  11. Art said,

    May 19, 2010 at 10:22 PM

    Thanks, it was very helpful for me.

  12. Charles Wieland said,

    June 8, 2010 at 3:31 AM

    Very useful, thank you!

  13. rpnanet said,

    June 23, 2010 at 1:45 AM

    Hi, you have link for solution in technet???

  14. July 16, 2010 at 6:32 PM

    thanks for the article. this query system sucks. may MS decide to use SQL in next version. your hints seem to point me in the right direction. however, I still get “One or more field types are not installed properly. Go to the list settings page to delete these fields.”. whom do they hire to author their ingenious error messages? as if I would’ve added extra fields just for fun so to just delete some afterwards, aaargh.
    did I mention this CAML/SP query architecture is cumbersome? 🙂

  15. Ivan said,

    September 9, 2010 at 8:58 AM

    How sort in by lookup field id?

    dosn`t work

  16. Ivan said,

    September 9, 2010 at 8:59 AM

    _FieldRef Name=’MacroRegion’ LookupId=’TRUE’ Ascending=’TRUE’ /_

    dosn`t work

  17. Chetan said,

    February 4, 2011 at 2:34 PM

    Very useful, thank you!

  18. MichaelL said,

    March 29, 2011 at 8:29 PM

    Life saving post, but I think you have a typo right?

    why do you need this twice?

  19. mswin said,

    April 23, 2011 at 12:02 AM


    I have to do the filtering for a multi value lookup column using CAML query.
    So, is this Syntax going to work even for the Multivalue lookup columns?
    Scenario is like in my case Country is a Mutli Select column, can I use the same above syntax for getting all the contacts which has India as one of the value for country in multi select column.

    Can someone confirm this.

    Thanks In Advance.

  20. April 26, 2011 at 7:27 PM

    […] per la dritta! This entry was posted in CAML Query, Uncategorized and tagged CAML Query. […]

  21. Sander said,

    July 6, 2011 at 12:34 PM


  22. siri said,

    October 13, 2011 at 4:36 PM

    Can some body help what is the formula for 12 months rolling data dynamically depending on current 12 months from today

  23. bushan said,

    October 19, 2011 at 6:38 PM

    How to pass the site log in name(Different users) to CMAL Query and assigning the out put to Filter lookup control..

  24. August 5, 2013 at 5:30 AM

    There are a few interesting vaccine trials around the world as well as a few potential cures being researched.
    Witch Hazel can also be applied on sores which will give you a relief.

    The natural treatment of cold sores can be very fast and

  25. site said,

    April 28, 2014 at 10:04 AM

    Wonderful web site. Plenty of helpful information here.
    I am sending it to a few buddies ans additionally sharing in delicious.
    And of course, thanks to your sweat!

  26. October 29, 2014 at 4:14 AM

    Always nice to find a helpful link! I am using this in SharePoint 2013 with the JavaScript Object model

  27. December 27, 2014 at 3:10 AM

    Any condition which affects the way we look can be upsetting, but not only that, any movement of
    the mouth can be extremely painful and as we have no option but
    to eat, drink and speak, there is very little we can do to avoid
    this. This helps focus your mental energy while at the same time relaxing the rest of your body.
    Another easy treat for dry crumbled skin is
    controlling your diet program.

  28. Guy said,

    February 4, 2015 at 7:15 PM

    If I have to ask, I should probably not be reading this article, but how and where exactly does the CAML above get applied? The field definition for the list, the view definition, where?

  29. Arul said,

    September 26, 2015 at 8:18 PM

    Is it possible to write as below when it approaches threshold?


  30. Arul said,

    September 26, 2015 at 8:19 PM


  31. Ole Albers said,

    October 19, 2016 at 2:02 PM

    small error in there: The line “” is there **twice**

    • Ole Albers said,

      October 19, 2016 at 2:03 PM

      Got filtered because of markup. It was
      FieldRef Name=”RefCountry” LookupId=”TRUE”
      In the second example

  32. Dzung said,

    April 23, 2018 at 8:34 AM

    Hi, I had a question that how to query the data with lookup value greater or lessthan some value. Like x => x[“Status”] > (DataTypes.LookupValue) “1”.
    Thank you in advance 🙂

  33. Alex Solo said,

    October 8, 2018 at 8:58 PM

    Thanks !

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: