Extract data from a table (for different variables)

[ksk_10594232]

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?

Thanks in advance.

[Werner_E]

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.

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

[ksk_10594232]

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?

[Werner_E · Accepted solution]

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.

[ksk_10594232]

Thanks you for a good explanation ! 🙂

[ksk_10594232]

I have applied the same methodolody as you have already demonstrated. However, as I put this into practice, one table is working effortlessly but another, which imo is almost identical,  is not working. What does the error message mean? 

See attached examples. Thanks! 

[Werner_E]

EXP should contain strings like "XC2", but you set it up to return two values