Display result base on condition

[yangc10]

I have multiple sheets, but let's focus on only two and I'll work on the rest.

The Main Sheet contains Pokemon cards that I owned. One of the columns is 'Market price'. I like the 'Market Price' column to get its result from the matching sheet names and 'Grade' type. The Sheet names can be found in the column 'Description'. 

For this example, Pikachu #TG05 (Row 1 on Main Sheet) Market Price should be $54.35 b/c the Graded column is G10.

On the Pikachu #TG05 sheet; the G10 column value is $54.35. 

My question is how do I update the Mark Price Column to reflect the mentioned condition? So the condition should be:

If other table name is equal to the Main['Description'] table then get value from other table that the column name is equal to the  Main['Graded'] table.

[NaveenGandhi]

Hello @yangc10 

Please tell me if the below solution would solve your problem.

Step 1

1. Add a column in Pikachu#TG05 table to denote the sheet name.
2. Except the sheet name column unpivot all the other columns, So that you have grade and price as two columns.
3. Create a Primary key by concatenating table name and grades. The final table should look like below.

Step 2

1. Create a Primary key in main table by concatenating description and graded column similar to above.

Now you have a unique identifier to create a relationship and make the calculation. The best part is you can make all the other tables similar to Pikachu#TG05 and append all of them together. So that you will only have 1 table with cards and price information and the main table.

Let me know if this helps or have any questions.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[yangc10]

would it be possible to do something like

IF Main["Description"] is equal to Pikachu[Name] then Main["Market Price"] = Pikachu[(Main["Graded"])

reason for Pikachu[(Main["Graded"]) is b/c we're trying to get the graded value from Main to grab the column names from Pikachu table.

[NaveenGandhi]

Yes it is possible as you ask for, But that's gonna be a tedious work to type the formula. But still you would need PK to do this.