21

I want to get 2 columns from another list and merge these columns. But when I am trying to do this via Lookup, they are not shown under "Add a column to show each of these additional fields".

But when I tried a simple text one (not lookup) it is shown.

Is there anyway to show a column which a kind of lookup field?

Improve this question

6 Answers 6

Reset to default
22

I gather that you're trying to use a lookup to 'bring in' additional fields to your current list, but that they aren't available to select.

Some field types are not available for this sort of function. For example, further Lookup fields can't be brought in.

Also, notably and annoying, neither can Choice fields. This was possible in SharePoint 2010 before SP1, but after SP1 it seems to be no longer possible. I can't see a good reason why, and it is very annoying.

There is no way of making additional types of column available in the look up control as far as I know.

Improve this answer
3
  • thanks Andy, i googled it alot and there is really no way to use looked up columns in additional fields. Its bad :/ I must find another way.
    – alicanbatur
    Commented Jun 27, 2012 at 10:44
  • 6
    +1 for the note on choice fields. Apparently a workaround is to copy the choice field to a calculated column
    – Christophe
    Commented Aug 29, 2013 at 22:57
  • Wow, this seems like a dumb limitation. I wonder why they made the decision to prevent Choice fields from being queried in a Lookup.
    – TylerH
    Commented Nov 27, 2017 at 20:32
8

How about this as a solution,

Say the choice column that you want to use is called 'My Choice', create a calculated column with the formula [My Choice] then this calculated column is available in the "Add columns to show additional fields" options. This will probably also do for a look-up column too.

It seems to work for me but I'm sure Microsoft will find a way of breaking this in the next service pack.

Improve this answer
5

It was very annoying not be able lookup currency column, however I found this workaround that worked in SP 2013.

  1. Create calculated column of type "Single Line"
  2. Use formula to format text as currency =TEXT([Price],"$0.00;($0.00)")
  3. Now this new column can be referenced as secondary column and visible in the list.
Improve this answer
4

I found a work around for this, what you do is use a wf to move the column into a text field. Go the origional list that has the information you'd like to look up. Creat a new text column and give it a name similar to the one you would like to dupilicate. Then create a WF to auto-populate that column into a text field. You can then look that column up in any other list.

Improve this answer
2
  • Yeah, this is what we do as well.
    – PeterX
    Commented May 22, 2013 at 6:15
  • 1
    From my vantage point, a calculated column to clone the choice field or lookup field is preferable to using a workflow, as the calculated column will be populated immediately upon saving the item, without the lag entailed in a workflow solution
    – Keith Hudson
    Commented Jul 12, 2017 at 19:24
3

Not all fields are supported as lookup fields. I tried to find a supported fields list but didn't find one. While searching though I ran across this discussion which offers a fix for columns that aren't supported such as choice fields.

http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/5a80988a-6b37-4454-957b-856ea16b2e60

Improve this answer
1

Not as far as I know - which is why it doesn't show.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.