Find a value in a table greater than the one I am searching

Mar 12th, 2002, 07:09 AM

I am taking a value from one cell and using Vlookup trying to find a value in a table which is greater than it. Vlookup always defaults to a lower value than the one I want!

Is there an IF function to attach to the Vlookup code to get the next value along, or is there a different approach that anyone can suggest?

Mark W. · Mar 12th, 2002, 07:36 AM

You could use...

=OFFSET($B$1,MATCH(A1,$B$1:$B$6,0),1)

...where your lookup table consists of 2 columns beginning in cell B1. Suppose your table contained...

{1,"A"
;2,"B"
;3,"C"
;4,"D"
;5,"E"
;6,"F"}

...and cell A1 contained 2. This formula would return "C".

Dave Hawley · Mar 12th, 2002, 07:47 AM

But zero as the Match_Type argument for match means find and exact match, so if the number doesn't exist you get the dreaded N/A

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 07:09 AM	#1
Guest Posts: n/a	I am taking a value from one cell and using Vlookup trying to find a value in a table which is greater than it. Vlookup always defaults to a lower value than the one I want! Is there an IF function to attach to the Vlookup code to get the next value along, or is there a different approach that anyone can suggest?

Mar 12th, 2002, 07:36 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	You could use... =OFFSET($B$1,MATCH(A1,$B$1:$B$6,0),1) ...where your lookup table consists of 2 columns beginning in cell B1. Suppose your table contained... {1,"A" ;2,"B" ;3,"C" ;4,"D" ;5,"E" ;6,"F"} ...and cell A1 contained 2. This formula would return "C".

Mar 12th, 2002, 07:47 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	But zero as the Match_Type argument for match means find and exact match, so if the number doesn't exist you get the dreaded N/A