Skip to main content
K
ksk_1059423212-Amethyst
12-Amethyst
April 18, 2023
Solved

Extract data from a table (for different variables)

  • April 18, 2023
  • 1 reply
  • 3437 views

Hi 

I want to extract a given value from a table based upon two variables. For exampel, by selecting the variables "A" and "b" I want the extracted value (output) to be "5". Is this possible to do?

ksk_10594232_1-1681807216966.png

 

Thanks in advance.

 

 

 

Best answer by Werner_E

Unfortunately Prime changes file format with every version and is unable to save files in a way so that they could be opened with older versions.

User friendliness made by PTC - perhaps one day PTC will realize that greed is not good advice.

 

But you can see in the picture how to do it in Prime. Simply add the "table" output to the Prime Excel component and retype what you see in the picture.

 

For the solution in Excel I used the Prime input region to get your input values in cells A6 and A7 as you can see in the picture.

I added a formula in cell A9 which gets the row number : =SUMPRODUCT((A1:A5=A7)*ROW(1:5))

and another one in cell A10 to get the desired result: =HLOOKUP(A6;B1:E5;A9;0)

This cell A10 is then added as output as you can see in the picture.

There sure may be simpler and more elegant ways to get the value in Excel - especially when you use a newer version of Excel which already provides new functions like XMATCH.

 

1 reply

W
Werner_E25-Diamond I
25-Diamond I
April 18, 2023

You have, as I see it, two options:

Either let Excel do the lookup job or extract the data table into Prime and let Prime look for the value.

Find attached a sheet which does both.

I also had to modify your dropdown lists! The first column in their table are just the values which are displayed. The second column are the values which are returned. As you had no values there, regard what you have chosen an empty string was returned.

You can leave the first column empty and then the values in the second column are used for display.

Werner_E_0-1681814865925.png

 

Depending on your needs you can also turn the Prime calculation into a handy utility function

Werner_E_1-1681814926231.png

 

 

K
ksk_1059423212-AmethystAuthor
12-Amethyst
April 18, 2023

Thank you!

 

Unfortunately, I can not open your attached file since the version is more recent. Could you please attach a file compatable for Mathcad Prime 7?

W
Werner_E25-Diamond IAnswer
25-Diamond I
April 18, 2023

Unfortunately Prime changes file format with every version and is unable to save files in a way so that they could be opened with older versions.

User friendliness made by PTC - perhaps one day PTC will realize that greed is not good advice.

 

But you can see in the picture how to do it in Prime. Simply add the "table" output to the Prime Excel component and retype what you see in the picture.

 

For the solution in Excel I used the Prime input region to get your input values in cells A6 and A7 as you can see in the picture.

I added a formula in cell A9 which gets the row number : =SUMPRODUCT((A1:A5=A7)*ROW(1:5))

and another one in cell A10 to get the desired result: =HLOOKUP(A6;B1:E5;A9;0)

This cell A10 is then added as output as you can see in the picture.

There sure may be simpler and more elegant ways to get the value in Excel - especially when you use a newer version of Excel which already provides new functions like XMATCH.

 

Terms & ConditionsAccessibility statement