Query an excel table to see if a value exists

[EP_10996709]

working on a table with a long list of items. need to query the table to see if an item is listed without erroring out every time.  

how can I check to see if "Black" is on the list and return a "N/A" or something I can use?

Any help will be appreciated.  Ed.

[Werner_E · Accepted solution]

You may use "try... on error ..." in your function SPBKUP to catch the error and return an appropriate result like NaN or a text message.

You may also consider using just lookup in your function ColNo instead of that combination of hlookup and match..

Actually I see no need for the function ColNo.

You could simply use

Of course you can replace indices 0 and 1 by ORIGIN and ORIGIN+1 to make the function ORIGIN independent.

For further help and future questions pls attach your worksheet and state which version of Prime you are using.

[EP_10996709]

Working with Prime 8.   see attachment. should be moving to 10 next week. 

[StuartBruff]

As Werner says, you can use the try programming operator.  One possibility for a not-found value you can easily test and which has certain other advantages (and disadvantages) is the NaN, meaning Not a Number.   You can use the built-in function IsNaN to check whether a value is a NaN.

Mathcad Prime 10 worksheet attached.

Stuart

Mathcad Prime 10 (and probably 8 ) Help on NaN.

About the NaN (Not a Number) Built-In Constant

The built-in PTC Mathcad constant NaN represents a missing or faulted value. It is useful for representing missing values in data sets, because it can be an array entry that you can easily distinguish from other matrix contents. It is also useful because PTC Mathcad matrices cannot have empty placeholders. The constant undefined represents missing values in symbolic calculations.

NaN and undefined

•The numeric value of NaN is NaN. In other words, if you type NaN= in a worksheet, PTC Mathcad returns NaN and assigns it the Constant label.

•When you evaluate NaN symbolically, the result is the undefined constant.

•The numeric value of undefined is NaN. In other words, if you type undefined= in a worksheet, PTC Mathcad returns NaN and assigns it the Constant label.

NaN Related Function

You detect NaNs using the IsNaN function, because boolean comparison operations can only be used on numbers. If you redefine the constant name NaN to some other value, it is no longer a NaN, and the IsNaN function returns 0. A NaN can only be processed by functions specifically written to use it. Other functions may return errors or incorrect results if passed a NaN.

The NaN-related functions:

•Work exclusively with the NaN built-in constant.

•Write the NaN built-in constant to one or more specified array elements. Otherwise, the functions read one or more specified array elements and check their content for the NaN constant.

•Can filter out specified rows that contain the NaN constant.

•Ignore anything that is not a NaN constant — such as numbers or strings.

Related Topics 

Expression Type Functions

About Outliers and NaN Functions

NaN Detection and Removal

Data Files

[Werner_E]

So I wonder why you did not try the function I posted?

It works as expected:

[StuartBruff]

---

@Werner_E wrote:

So I wonder why you did not try the function I posted?

It works as expected:

---

Given black is often defined as the absence of colour and any colour not in the set {Red, Blue,Green,Yellow} is absent, would "Black" be a suitable value for an absent colour?

Stuart

And people wonder why a) I spend all night awake cogitating over the whichness of the what and the whyness of the where, b) I never get invited to parties ... except by philosophers, and who wants to attend those kind of parties?  Not even Professor Pettigrew doing her famous dance of the ℵ_α veils could entice me to such a party (at least they're countable, which is something, I suppose).  Of course, if they had some decent beer, that might be another matter ... maybe if somebody could perform a Banach–Tarski operation on the beer barrel ... Hmm.  I'll have to think about that one.  Ah well, time for a coffee, I suppose.

.

[EP_10996709]

both work for me as I have several different requirements from large tables.  Thanks to both of you on this.