Array problem. Comparing data.

  • Thread starter Thread starter Erwin
  • Start date Start date
E

Erwin

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!
 
Assuming that the numbers in column A are sorted, as implied by your
example, then this in D1:

=INDEX(B:B,IF(C1<=A$1,1,MATCH(C1,A:A)+1))

and then copied down will give you what you want. This assumes your
data starts in row 1 - if it starts in row 2 (with headers in row 1)
the formula in D2 should be:

=INDEX(B:B,IF(C2<=A$2,2,MATCH(C2,A:A)+1))

Hope this helps.

Pete
 
Back
Top