Excel formula help

October 26, 2011 10:56 AM

Help me write an Excel formula to extract specific data from a list?

I have two lists of numbers, unequal in length and different in content, that I am trying to map against each other in the following way. Let's call them list A and list B. I'm trying to write a formula that, for every item on list A, returns the lowest number on list B that is higher than that item. So suppose my lists were:-

A: 3, 7, 9, 16

B: 1, 2, 4, 15, 18, 23

the formula would return:-

A....Formula
3....4
7....15
9....15
16...18

I'm sure this is a trivial problem in vba, but I've never learnt it and to be honest don't have the time to do so now. But I'm equally sure there's an elegant array formula that will do this, probably using a MIN function, but I can't get the syntax right. Can someone help me with this?

It's easy with DMIN, by the way, but DMIN will only output one item of data at a time and not in a column against each item (... unless you know different!), and this is essential as I have further analysis to do on the data after this stage.

Using Excel 2003, by the way

posted by genesta to Computers & Internet (8 answers total)

Best answer: ={MIN(IF(B:B>A1, B:B,""))}
posted by rube goldberg at 11:22 AM on October 26, 2011

How are the data structured in the Excel file? Do you have one comma-separated list per cell, or are they split into separate cells?

How do you envision the output? A five-line string like your example, or something else?
posted by Doofus Magoo at 11:33 AM on October 26, 2011

Response by poster: Rube - you rock! A simple and elegant array formula as I suspected. So obvious once you see it! I've been close for half the afternoon, but not quite there - thank you so much.

Doofus, thanks for the offer of help but Rube's there already.
posted by genesta at 11:39 AM on October 26, 2011

When you get a sec, could you tell me how your data is displayed in order for Rube's to work?

I had a different solution just ready to post -- which may just be a more complicated, less Excel knowledgeable way of doing the same thing -- but when I try rube's, I can't get it to work.
posted by MCMikeNamara at 11:47 AM on October 26, 2011

If the A list is in column A, and the B list is in column B sorted large to small (order matters), the following function in column C would return the value you're looking for:

=INDEX(B:B,MATCH(A1,B:B,-1))
posted by jsturgill at 11:48 AM on October 26, 2011

Rubes formula is an array formula which means it has to be entered with CTRL+SHIFT+ENTER...that may be why it doesn't work for some....
posted by dfriedman at 11:52 AM on October 26, 2011

Glad that worked for you. And sorry to others for the absent explanation. I presumed that the A and B lists were actually columns with one value per cell, starting at row 1.

The IF function in the formula creates an array of all values in the B list that are greater than the current cell in the A column. For example, if cell A1 contains 3, then the IF function creates this array {"";"";4;15;18;23}.

The MIN function then just returns the minimum of that array.
posted by rube goldberg at 12:29 PM on October 26, 2011 [1 favorite]

Response by poster: Rube and I are clearly on the same wavelength - his assumptions about my data are correct, and his explanation is a model of clarity. I'm not going to muddy the waters by trying to add to it.

For anyone interested, here's the reason for the question. I have a large number of staff paid, for historical reasons, on two quite different salary scales. I'm looking at eliminating one of these scales. So I need to know how much it would cost to move all those on scale A onto scale B - which will inevitably entail moving them to the nearest point on the new scale in an upwards direction. Rube's formula allows me to model this and calculate the cost.
posted by genesta at 12:47 PM on October 26, 2011