Ask Your Question
0

[Solved] Need Vlookup to return smallest value greater than or equal to search criterion

asked 2017-06-12 03:56:17 +0100

Havaneiss Dei gravatar image

updated 2017-06-12 14:13:46 +0100

Example:

A1 = 26

A2 = 80

A3 = 95

B1 = 950

B2 = 1500

B3 = 1650

C1 = 99

C2 = 3

=VLOOKUP (C1/C2,A1:B3,2,1)

returns 950

I need it to return 1500. However, if C1=52 and C2=2, I need it to return 950.

I am not stuck on using VLOOKUP: I just need a formula that will work!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-06-12 14:15:59 +0100

Havaneiss Dei gravatar image

updated 2017-06-12 14:17:24 +0100

Hi!

=IF (ISNA (VLOOKUP (C1/C2,A1:B3,2,0)=1,INDEX(B1:B3,MATCH(C1/C2, A1:A3)+1),VLOOKUP (C1/C2,A1:B3,2,0))

seems to work. Thanks for the help!

edit flag offensive delete link more

Comments

Please mark correct answer with checkmark

Mike Kaganski gravatar imageMike Kaganski ( 2017-06-12 14:22:23 +0100 )edit
0

answered 2017-06-12 09:15:45 +0100

karolus gravatar image

Hallo

=INDEX(B1:B3,MATCH(C1/C2, A1:A3)+1)
edit flag offensive delete link more

Comments

Hi! That seems to be a step in the correct direction, but it chooses the higher value even when C1/C2 = A1.

I ended up using:

=IF (ISNA (VLOOKUP (C1/C2,A1:B3,2,0)=1,INDEX(B1:B3,MATCH(C1/C2, A1:A3)+1),VLOOKUP (C1/C2,A1:B3,2,0))

Thanks for the help!

Havaneiss Dei gravatar imageHavaneiss Dei ( 2017-06-12 14:01:29 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-12 03:56:17 +0100

Seen: 58 times

Last updated: Jun 12 '17