selecting next row (R+1) using VLOOKUP

[njbourque]

I am wanting to write a formula using VLOOKUP that when the values in the selected range are gone through the default instead of being the next lower value that the LOOKUP selects from it goes to the next higher value. Example

Col A Col B
100 1
200 2
300 3
400 4
500 5

if my value to lookup is 250 the return value is 2 I want it to be 3. I do not want to write code and was hoping a function might be all that is needed.

 

[T. Valko]

I am wanting to write a formula using VLOOKUP that when the values in the selected range are gone through the default instead of being the next lower value that the LOOKUP selects from it goes to the next higher value. Example

Col A Col B
100 1
200 2
300 3
400 4
500 5

if my value to lookup is 250 the return value is 2 I want it to be 3. I do not want to write code and was hoping a function might be all that is needed.

Book1
	A	B	C	D	E
1	100	1	_	250	3
2	200	2	_	_	_
3	300	3	_	_	_
4	400	4	_	_	_
5	500	5	_	_	_
Sheet1

This array formula** entered in E1:

=INDEX(B1:B5,MATCH(TRUE,A1:A5>=D1,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Oaktree]

If you sort your list in descending order, you could use =INDEX(B1:B5,MATCH(250,A1:A5,-1))

 

[mikerickson]

if column A is sorted ascending
=LOOKUP(250, A1:A5, B2:B6)

 

[njbourque]

Book1
*	A	B	C	D	E
1	100	1	_	250	3
2	200	2	_	_	_
3	300	3	_	_	_
4	400	4	_	_	_
5	500	5	_	_	_
Sheet1

This array formula** entered in E1:

=INDEX(B1:B5,MATCH(TRUE,A1:A5>=D1,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Apologies for expecting all those that replied to read my mind.....the range of values is located on a different worksheet and the value I want to use is the result of an equation.
My formula on sheet1 is =VLOOKUP($K9,'Sheet2'!$A$2:$C$26,3), the value that appears in Cell K9 is the result of an electrical formula and the range of cells on 'Sheet2' is a table from the electrical code wire gauge chart. My formula gives me the power draw in amps and I wanted the next cell L9 (on sheet1) to display the wire size for that calculation. I did not want to have the table on sheet2 displayed. As my example explained (somewhat) the VLOOKUP result I get from my formula selects the lower wire size when an exact match is not found, which is not what I want. Can I achieve the same result using Index:Match on different worksheets? If so can some one instruct me....BTW thanks for the responses, many and to the point, awesome.

 

[T. Valko]

Apologies for expecting all those that replied to read my mind.....the range of values is located on a different worksheet and the value I want to use is the result of an equation.
My formula on sheet1 is =VLOOKUP($K9,'Sheet2'!$A$2:$C$26,3), the value that appears in Cell K9 is the result of an electrical formula and the range of cells on 'Sheet2' is a table from the electrical code wire gauge chart. My formula gives me the power draw in amps and I wanted the next cell L9 (on sheet1) to display the wire size for that calculation. I did not want to have the table on sheet2 displayed. As my example explained (somewhat) the VLOOKUP result I get from my formula selects the lower wire size when an exact match is not found, which is not what I want. Can I achieve the same result using Index:Match on different worksheets? If so can some one instruct me....BTW thanks for the responses, many and to the point, awesome.

I'm pretty sure the formula I suggested will do what you asked for. Just include the appropriate sheet name to any range references.

 

[bebo021999]

You can use this:

=INDEX(B1:B5,MATCH(C1,A1:A5,1)+1)

With C1 = 250

 

[T. Valko]

You can use this:
=INDEX(B1:B5,MATCH(C1,A1:A5,1)+1)
With C1 = 250

That won't work when there's an exact match. You'll get an incorrect result.