# Thread: Locating the source of the largest/smallest change

1. ## Locating the source of the largest/smallest change

I'm playing around with an example of Array formulas from John Walkenbach's Power Programming with VBA (Excel 2000)

The example is like this:

<table border=1><td> </td><td>A</td><td>B</td><td>C</td><td>1 </td><td> </td><td>Test 1</td><td>Test 2</td><td>2</td><td>Student 1</td><td>45</td><td>56</td><td>3</td><td>Student 2</td><td>78</td><td>73</td><td>4</td><td>Student 3</td><td>91</td><td>93</td><td>5</td><td>Student 4</td><td>62</td><td>69</td><td>6</td><td>Student 5</td><td>74</td><td>71</td></table>

{=MIN(C2:C6-B2:B6)} will return the value of the smallest increase (or decrease) between test 1 and test 2.
So here is the natural next question. Is there a way to have the formula return the row that the smallest increase is in?
I've played around with Lookup and Match, but these would all require that the values are sorted in ascending order, which would mean resorting the students all the time.
Just a question....

2. ## Re: Locating the source of the largest/smallest change

Say that the formula returning the minimum is in cell F1.
The following array formula will return the index (relative row number within B2:C6) of the minimum: =MATCH(F1,C2:C6-B2:B6,0)
If you want the absolute row number, use the array formula =ROW(INDEX(B2:B6,MATCH(F2,C2:C6-B2:B6,0)))

3. ## Re: Locating the source of the largest/smallest change

Hi Catharine,
Try <code>{=MATCH(min(C2:C6-B2:B6),C2:C6-B2:B6,0)}</code>
HTH

4. ## Re: Locating the source of the largest/smallest change

I didn't fully complete my example, if you try and find the max using the match function - this is when it needs to be sorted.
Works ok for min tho' must be the way my data is ordered...

5. ## Re: Locating the source of the largest/smallest change

I can't get your =ROW(INDEX(B2:B6,MATCH(F2,C2:C6-B2:B6,0))) formula to give me anything but an NA ERROR (tho' the function wizard previews the correct value <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> ) and could we get it to function for max values (see my answer to Rory).

6. ## Re: Locating the source of the largest/smallest change

<code>{=MATCH(MAX(C2:C6-B2:B6),C2:C6-B2:B6,0)}</code> works fine for me, returning 1. Do you get an error?

7. ## Re: Locating the source of the largest/smallest ch

Hmm, wait a minute now its working....
I'd better go to bed and check if it still works tomorrow. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>