• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a value in a range of 2 colomns

R

RobinSC

New Member
Hi,

I'm trying to create an automatic configurationsheet for solarpanels. I'm trying to find a formula if a certain value is higher than column A and lower that column B, to give Column C. If there are multiple possibilities, I should get the option with the lowest number.

For example the value is 2160, Option 3 and 4 are possible, but I should get Option 3 (according to attached file).

In attached file I used a nested if function for now. I do want to extend this excel sheet so a nested if function will be tricky.

I'm looking for a formula (something like index and match) where I can directly find the needed row and select the option is the array F4:I14 (for this example).

Could you help me on this?

Thanks in advance,

Kind regards,
Robin
 

Attachments

Hi:

If you are looking for only the one option as you mentioned in your post
For example the value is 2160, Option 3 and 4 are possible, but I should get Option 3 (according to attached file).
Click to expand...

Use the following formula
Code: 
=IFERROR(INDEX($I$4:$I$14,MATCH(1,(A4>=$F$4:$F$14)*(A4<$G$4:$G$14),0),1),"N/A")

Array formula execute it with Control+shift+enter key.


Thanks
 

Attachments

Nebu said:
Hi:

If you are looking for only the one option as you mentioned in your post


Use the following formula
Code: 
=IFERROR(INDEX($I$4:$I$14,MATCH(1,(A4>=$F$4:$F$14)*(A4<$G$4:$G$14),0),1),"N/A")

Array formula execute it with Control+shift+enter key.


Thanks
Click to expand...

Hi Nebu,

This is exactly what I was searching for, thank you. I didn't knew this Control+shift+enter function yet, but this will be very helpful in my further calculations.
 
Hi Robin,

you can also try this one with giving 3 finger power (CTRL+SHIFT+ENTER)at the end of the formula and also thanks Nebu sir for his efforts to solve your query
with the formula which is also new for me..

=IFERROR(INDEX($I$4:$I$14,SMALL(IF(A4>=$F$4:$F$14,IF(A4<$G$4:$G$14,ROW($I$4:$I$14)-3)),1)),"NA")
 
Hello friends,

Two other solutions, without CSE,

=INDEX($I$4:$I$14,MATCH(1,MMULT((A4>=$F$4:$F$14)*(A4<$G$4:$G$14),1),0),0)

=INDEX($I$4:$I$14,AGGREGATE(14,6,ROW($I$4:$I$14)-3/((A4>=$F$4:$F$14)*(A4<$G$4:$G$14)),1),0)

David
 
Thanks for your reaction guys. I'm going to use the MMULT function for my excelsheet. The formula of Nebu is nice but as soon as the value fits in two rows, I'm getting an error (#N/A). Furthermore it is easier for other people to work with my sheet if I don't have to use CSE (they don't have all as much experience as you guys do ;) )

Kind regards,
Robin
 
You must log in or register to reply here.
Back
Top