Need to Look up items from same list and return back on data on form

There are a few often overlooked 'gotchas' when trying to use the Lookup runtime function with a Lookup Control value. 

Lookup Controls do not return just the text value of whatever it is you have selected from the dropdown. The value is actually a combo of the Item's ID (for whichever item you had selected from the dropdown) + the Value of the column the Lookup is targeting. 

You can see this easily if you create a Calculated Control, and have it's value populate with the value of your Named Lookup Control. 

Here is a small list of items

In a form if I make a Lookup Control and then show that value in a Calculated Control directly next to it, this is what I'm talking about: 

We see the ID of the Item, a delimiter of ";#" and then the actual value we're concerned with "PSE-000003"

So how do we get just the last part of the value? We can use the parseLookup runtime function!

Here is another Calculated Control's formula to show how it's setup:

The results (parseLookup calc control is on the far right): 

So now that we know how to get the value that we actually wanna use, let's setup a Lookup inside of a different Calculated Control.

And here is the formula: 

The arguments described in order are:

1. The Target List


2. The column you're checking your value against


3. The Value


4. The column from which you'd like return a value IF a match can be found

You may notice the column names I'm using look kinda strange. That's because SharePoint requires you to use the "Internal Column Name" of a column when you're making a CAML Query as this is doing in the background. To get your Internal Column Name, go to your List Settings: 

And then find and click on the column(s) in question: 

In the Column Settings menu, look up at the URL of your browser and go to the very end of it. There you will be able to see the Internal Column Name of your column in question!

Once you do that for both of your columns, you should be good to go! 

If you get back to your form and test it out but don't see any results, the last thing you can do is to open up the Browser Dev Tools by pressing the F12 button on your keyboard. 

Whenever a lookup operation happens on your form, it actually outputs information to the Console of your browser. By making a selection from our dropdown, we can see that the console is being populated with information about the Query being used that was based off of the lookup function

Formatted Query: 

If something were to be wrong we might also see error messages there. However if you're unsure whether or not your Query is valid, you can always go into a Query List, Workflow Action (in the Workflow Designer) and use the Run Now option to test and see exactly what's being returned. 

I hope this helps you to solve your lookup issues.