cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SopandaT Frequent Visitor
Frequent Visitor

Looking Up Value from Another Table and Searching Column

Hello,

 

I'm trying get a column to search for a specific value in another table and if it finds that matching value, it will take the value in a specific column and apply it to the new column. I basically have a table with data that is mixed up together based on the 'Work Item Type' so I duplicated the table and filtered the other table to only show a specific 'Work Item Type'. The duplicated table is linked by a releated Work ID to the first table. I'm pulling data from VSTS if that helps.

8 REPLIES 8
Super User
Super User

Re: Looking Up Value from Another Table and Searching Column

LOOKUPVALUE is probably your best bet.

 

https://msdn.microsoft.com/en-us/library/gg492170.aspx


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


SopandaT Frequent Visitor
Frequent Visitor

Re: Looking Up Value from Another Table and Searching Column

I'm curently using LOOKUPVALUE and I get the error message "LookUpValue does not support comparing values of type integer with values of type text".Does that mean one of my columns that I picked is not the same as the other formats? I've tired using the expression FORMAT on that numeric column but I don't know what to put for the second condition if that makes sense. 

FORMAT(Value,Format) I don't know what to put in format.

emadrigal Regular Visitor
Regular Visitor

Re: Looking Up Value from Another Table and Searching Column

why dont you create a copy of that column with the same datatype create the relation and then hide it? 

Super User
Super User

Re: Looking Up Value from Another Table and Searching Column

You could use VALUE to convert from text to numeric, would have to see the data to see what is going wrong there.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Looking Up Value from Another Table and Searching Column

Hi @SopandaT,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
jennympho Frequent Visitor
Frequent Visitor

Re: Looking Up Value from Another Table and Searching Column

the problem I had with LOOKUPVALUE function is that I could not reference to a column in a table under search value.  I wanted to look up the same value in another table in one column and return the result of another column in that another table.  Not sure how I could do it. 

Super User
Super User

Re: Looking Up Value from Another Table and Searching Column

@jennympho- Can you explain that again with maybe an example?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


jennympho Frequent Visitor
Frequent Visitor

Re: Looking Up Value from Another Table and Searching Column

@Greg_Deckler

 

Hi Greg,

 

this is how I have resolved the problem at the end but i wonder if there's a quicker method.

 

I have 2 tables.

Table 1 - has 2 columns - account number and amount

Table 2 - has 2 columns - account number and whether overhead cost applies to that account number

 

I wanted to create a formula that will add the overhead cost to the amount if it should.

 

So, now this is how I have figured out to solve the issue:

1.  I added a calcuated column and use a LOOKUPVALUE function in table 1 to see if overhead cost should apply

2.  I added another calculated column to calcuate the overhead cost (amount x overhead rate) if overhead cost should apply

 

I wonder if this can be done in 1 formula and in 1 calculated column.

 

thanks for sharing your knowledge in advance!
Jenny