Sorting data in the table with column that depends on the other table

Occasional Visitor

Hello,

I want to sort data in the table with column that depends on the other table and issue is that sorting it changes the results in the column that depends on that table.

 

In pic1 there are 2 tables sorted by Name tables but if I want to sort Table2 by column Value I get different results in column Formula Result. I simply want column Formula Result to stay the same as in pic1 no matter how it's sorted in Table2.

 

What can be done about this?

1 Reply

@CorrectSide You didn't show the formula in the result column, but if I assume it's one without a lookup function. In the picture below you can see how you can use VLOOKUP. 

Screenshot 2021-11-07 at 06.40.26.png

Or if your Excel version supports XLOOKUP, use this one in H3:

 

=XLOOKUP([@name],Table1[name],Table1[value])-[@value]