i x j matrices and vlookup function

[PI_7992201]

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]

---

- 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.

[PI_7992201]

Thank you for the quick reply.  Thanks for catching my error regarding the b matrix, it should have been 0.040 for each value.  The first reply got me close.  However, not all values in "a" are exactly in "c".  I was trying to modify the first reply to see if I could find a solution, and just noticed your second reply. I will keep working on this and let you know if this works for me.  Thanks.

[PI_7992201]

Does vlookup (or lookup) require an exact match?  I've modified "c" matrix to illustrate that not all values of "a" are represented in "c". And highlighted the values that are correct based on what I am trying to solve.

[Werner_E · Accepted solution]

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.

[PI_7992201]

Thank you very much.  It worked as expected on my larger matrices.