=Large Function - Multiple numbers

**afriedman** · 01-18-2011, 04:38 PM

I have looked through the forum and haven't found an exact answer so hopefully someone can help.

I have a set of data (Attached) which I am trying to get the largest number of visitors from the corresponding location. From there am using a vlookup to give me the corresponding city.

The problem is this:
Excel only recognizes the first in the series. So in this example the corresponding cities are San Francisco and LA, but it only retrieves San Francisco since it comes first.

Does anyone know how I can tweek it to give me the next one instead.

Or better yet, does anyone have a better idea how to get the data I am trying to get?

Thanks!

**daddylonglegs** · 01-18-2011, 04:45 PM

One way to do that is to use this "array formula" in F2

=INDEX(C$2:C$6,SMALL(IF(B$2:B$6=E2,ROW(B$2:B$6)-ROW(B$2)+1),COUNTIF(E$2:E2,E2)))

confirmed with CTRL+SHIFT+ENTER and copied down the column

**Fizziii** · 01-18-2011, 05:18 PM

daddylonglegs,

Thank you for the response. I have a post from earlier today located here. Your solution here would solve mine I think, except it's not working as expected. If I click on the fx button to check the formula it shows the correct result, but that's not what's appearing in my cells. Is this a bug with Office 2010? Please see my attached screenshot. I added a column that shows the results I'm getting, a column that has the formulas copied over with an apostrophe in front of them, and the fx dialog pulled up showing what it should say.

I know I have my own thread, but I haven't seen a response and your response here should fix my issue as well...

\1

**daddylonglegs** · 01-18-2011, 05:30 PM

You need to confirm the formula with CTRL+SHIFT+ENTER

Select the cell with the formula and press F2 key to select formula. Now hold down CTRL and SHIFT keys and press ENTER. If done correctly you'll get curly braces like { and } around the formula in the formula bar.

If you change the formula then you have to do that again.

Once you've "array entered" the first formula you can copy that down the column (all the others will be done automatically)

**Fizziii** · 01-18-2011, 05:32 PM

Thank you very much. I've given you positive feedback, and I'll mark my other thread solved and link back to this one.

**afriedman** · 01-19-2011, 12:08 PM

Daddylonglegs, Thanks for your help. It works great, but I guess I have a follow up question.

I am trying to dissect the formula you wrote out and make sense of it. Could you perhaps explain it a bit mroe in depth so I can recreate this in the future if I need to?

Thanks!

**Butterglider** · 03-22-2012, 03:38 PM

I know it's been a while since this thread was posted, but I would also find it tremendously helpful if daddylonglegs explained what each of part of his equation meant. It works very well for me, but I can't claim to understand it. Thanks

**daddylonglegs** · 03-22-2012, 04:06 PM

OK, I'll try to explain, I'm not sure why I didn't at the time.....

In the example given the problem is that using VLOOKUP gives the same result in F2 and F3 when that isn't required so my suggested solution is to use this "array formula" in F2 copied down

=INDEX(C$2:C$6,SMALL(IF(B$2:B$6=E2,ROW(B$2:B$6)-ROW(B$2)+1),COUNTIF(E$2:E2,E2)))

The key is the COUNTIF part at the end that will increment if the numbers in E2:E6 repeat.

For example in F2 the COUNTIF must always return 1 because it counts the number of E2 values in the single cell range E$2:E2, so the formula is the equivalent of this

=INDEX(C$2:C$6,SMALL(IF(B$2:B$6=E2,ROW(B$2:B$6)-ROW(B$2)+1),1))

The IF function tests whether B2:B6 cells are equal to E2 and if they are it returns the relative row number in the range, i.e. in F2 the IF part

IF(B$2:B$6=E2,ROW(B$2:B$6)-ROW(B$2)+1)

returns this array

{FALSE;FALSE;3;4;FALSE}

the 3 and 4 represent the matches, B4 and B5 which are the 3rd and 4th cells in B2:B6

Now SMALL with a second argument of 1 (supplied by COUNTIF) takes the smallest of those, i.e. 3 and INDEX then looks like this

=INDEX(C$2:C$6,3)

which returns the 3rd cell of C2:C6, i.e. C4 = san francisco

Now in F3 the IF part still returns the same array (because E2 = E3)

{FALSE;FALSE;3;4;FALSE}

....but now the COUNTIF part has changed to COUNTIF(E$2:E3,E3).....and because E2 = E3 that returns 2....so now SMALL takes the 2nd smallest value from that array, 4, so INDEX is now

=INDEX(C$2:C$6,4)

....returning the other 300 value, i.e. L.A. from C5

This works whether values repeat or not, e.g. in F4 the COUNTIF is now COUNTIF(E$2:E4,E4)....but E4 is 250 so the result of that is back to 1 and the formula returns the only city with 250 visitors (Chicago)