in [Programming]

Prev: Help with SORT property in xl2007 and xl2007 !!!!!!!!!!!!!!!!!!!!
Next: Check to see if subtotal is expanded
From: Erwin on 29 Apr 2010 02:07 Hey, Let me set up what I'm trying to do. I have 3 columns with the following data. A: 10, 20, 30 B: X, Y, Z C: 28, 8, 1 Now, I have a last column, D, which I'm trying to figure out the correct formula for. I want D to return the following: Look at column A, find the smallest number that's greater than the adjacent cell in column C, and then return the adjacent cell in column B. So, in this example, column D should read: D: Z, X, X I can't figure this out. I think it has to do with arrays, but I'm not too sure. If anyone has any insight on how to accomplish this, I would really appreciate it. Thanks so much!
From: Jacob Skaria on 29 Apr 2010 03:10
Hi Erwin Try the below in cell D1 and copy down as required. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}" =VLOOKUP(SMALL(IF($A$1:$A$10>C1,$A$1:$A$10),1),$A$1:$B$10,2,0) -- Jacob (MVP - Excel) "Erwin" wrote: > Hey, > > Let me set up what I'm trying to do. I have 3 columns with the > following data. > > A: 10, 20, 30 > B: X, Y, Z > > C: 28, 8, 1 > > Now, I have a last column, D, which I'm trying to figure out the > correct formula for. > I want D to return the following: > Look at column A, find the smallest number that's greater than the > adjacent cell in column C, and then return the adjacent cell in column > B. > > So, in this example, column D should read: > D: Z, X, X > > I can't figure this out. I think it has to do with arrays, but I'm not > too sure. If anyone has any insight on how to accomplish this, I would > really appreciate it. Thanks so much! > . > |