Reading an Excel file to an array and then programming issues

[ptc-5452670]

Hey guys,

I've read two columns of an excel file to a mathcad file that looks something like:

0	1
1	a
2	b
3	c
4	d

I have a series of equations set up in a program, and i have a while loop set up as:

{{While (b[a,0 < f(x))

{{a=a+1

{{b[a,1

Where b represents the array made from the excel file and a's initial value is zero. I want it to read the value of column zero and if the data from the function doesn't match the value in the current row, I want the program to loop and read the next row. When the value from the equation matches the value from the array, I want the program to return the corresponding value from the same row in column 1.

It keeps returning an error message saying "This array index is invalid. The index must be an integer, not less than the ORIGIN, and not greater than the last element." And there is a red box around the a,0 in b[a,0.

Any help or advice here would be greatly appreciated!

[MikeArmstrong]

Please upload the worksheet.

[ptc-5452670]

I can't really upload the excel file to the internet. It has sensitive material that I'm not at liberty to share with people

[MikeArmstrong]

You could always replicate the problem and post a dummy worksheet.

[Fred_Kohlhepp]

Something like this?

[ptc-5452670]

I'm not 100% sure as I'm using prime 2.0. I'm not really familiar with this flavor of mathcad. However if your Program(1,excel)="a" are similar to Werner's Lookup executions then yes that worked. I dont completely understand the programming section written above it though it does look similar to mine.

[Werner_E]

Your explanation is different from the program fragment you show.

Obviously you assume that the first column is in ascending order. When should be triggered: If the value of b[a,0 >= f(x) as your program says or if b[a,0 = f(x) as your text says? What should happen if no value in the first column is equal to f(x)? What if all values are smaller? Error message, NaN, ...?

Ferd showed one way following the code you provided. Another way would be to use vlookup() or Vlookup(). Which one of the two would be appropriate depends on the answers to the questions above.

Its hard to tell what throws the error you described, you would have to attach your worksheet. Best guess is that f(x) is greater than all values in your first column and so a will be incremented until its greater than the number of the last row - then the error is thrown. To avoid that Fred has inserted that a<N constraint.

[ptc-5452670]

I used these lookup functions and they returned an answer that I need. Thanks very much!!!