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!
> .
>
 | 
Pages: 1
Prev: Help with SORT property in xl2007 and xl2007 !!!!!!!!!!!!!!!!!!!!
Next: Check to see if subtotal is expanded