cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How to search a Matrix for a number bounded by another number?

SOLVED
Highlighted
Participant

How to search a Matrix for a number bounded by another number?

In the attached Prime worksheet is it possible to create a function that can search the second row of the matrix for the a number closest but higher than ZxREQ while also making sure that it's corresponding value in the first row is between dmax and din? The issue I'm having is how do you search in a particular row in a matrix?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to search a Matrix for a number bounded by another number?

Changed the sample data

Simplified the first method.

Changed all methods to use <= rsp. >=

Added a third, tricky method

2.png

17 REPLIES 17

Re: How to search a Matrix for a number bounded by another number?

From the file you posted it looks like you are searching a value in the second column, not in the second row.

BTW, what should the outpit of that function be? A list(vector) of indices as Match would return? The first value of the second column which fits? A vector of all values of the second column which fit? The samllest value in the second column that fits? ...

Re: How to search a Matrix for a number bounded by another number?

You're right, I did mean the second column.

The function would output the corresponding value in a third column which I will add.

Re: How to search a Matrix for a number bounded by another number?

Then you should setup a sheet with complete data!

So the value in the second column should be > (or >= ) ZxREQ?

The value in the first column should be between d.min and d,max. Usually this would exclude values equal to d.min or dmax. Is that correct?

The output cant be just a value but rather a vector of corresponding values from the third column as it may not be only a single value which fulfills tghe conditions.

Re: How to search a Matrix for a number bounded by another number?

Here are two different ways of achieving what you demanded so far (no error checking implemented so far):

1.png

Re: How to search a Matrix for a number bounded by another number?

Changed the sample data

Simplified the first method.

Changed all methods to use <= rsp. >=

Added a third, tricky method

2.png

Re: How to search a Matrix for a number bounded by another number?

Thank you for your help Werner, is it possible to create a vlookup that looks in a specified column, not just the first column?

Re: How to search a Matrix for a number bounded by another number?

Daniel Sochor wrote:

Thank you for your help Werner, is it possible to create a vlookup that looks in a specified column, not just the first column?

Sure, look at the last two approaches I posted and change the 0 index to whatever column you want.

When you try to develop a generic new routine I guess the most labourious part would be to implement the various modifiers ("range", "neq" ,....).

Re: How to search a Matrix for a number bounded by another number?

This is a very elegant solution. Can it only return integers or can it be modified to return text in parenthesis?

Re: How to search a Matrix for a number bounded by another number?

Daniel Sochor wrote:

This is a very elegant solution. Can it only return integers or can it be modified to return text in parenthesis?

I am not sure what you mean?

If the values in the column vlookup should return are strings, you will get a string as result.

If you are happy with "eq" and don't need the other modifiers, here is a vLookup as you requested:

1.png