i x j matrices and vlookup function

Question

I am having trouble figuring out how to use LOOKUP with matrices. I am using Prime 10.0.

"a" is an i x j matrix generated from numerous previous calculations.

"b" is also an i x j matrix, with each value being a constant in the attached file, but will also be non-constant in future calculations.

"c" is a 2 column matrix that is generated by reading a 2 column Excel table.

What I am trying to generate an i x j "d" matrix that is calculated as follows:

- if value in "a" is less than the value in "b" (same position in each matrix), then lookup the "a' value in the first column of "c" and return the value in the associated 2nd column in "c", or

- if value in "a" is greater or equal to the value in "b" return zero.

If tried a couple of different methods, but either get an error with the vlookup function (see attached), or i get all the values in the i x j "c" matrix equal to the calculated results of the "a" matrix 0,0 position (although calculated correctly for the 0,0 position).

Thanks in advance.

Werner_E · Accepted Answer

You can add a modifier to the various lookup function (like "near", "geq" etc.) or define your own function. Look it up in the help.

Without a modifier an exact match is required - "exact" means within a Tolerance based on the system variable TOL.

You have to decide which value should be used when the a-value is 0.36 or 0.38.

From what you wrote it seems that you want to use the next smaller value. So this modification should do the job:

The modifier "leq" forces the function to return a vector with all values equal or less than the given one. We need the last value of this list, so I reverse the vector and take its first element (index 0).

An alternative would be

but I prefer the first one.

Werner_E · Answer

- if value in "a" is less than the value in "b" (same position in each matrix), then lookup the "a' value in the first column of "c" and return the value in the associated 2nd column in "c",

None of the values in matrix a are present in the first column of matrix c!!

I changed matrix c so it now contains all values of matrix a which are smaller than 0.04

Are you looking for something like this?

"replace" is written to do the exchange of values according to your rules. This function is then called vectorized with the full matrix as argument.

The method only works because the first column does not contain the value 0.04.

If this does not apply to your real data, then please provide more realistic test data.

EDIT: A version which may come nearer to what you need is this

There is no error check if the table c does not contain the necessary value.

This could be done with a try and catch

The looping through the rows and columns is done by the vectorization operator which is why we can write it that short.

Of course you could program these loops yourself - here one possible way to do it. Error check for missing value in c included.

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded