Lookup returning multiple (incorrect) values

[seed]

I started with the code from here:
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

My current formula is
=INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2)

I don't fully understand how the row function works, specifically how the
ROW(1:1) statement plays into the SMALL function or even what ROW(1:1)
signifies, and because of this I can't figure out why the whole formula is
basically doing things right but instead of identifying occurrences of A$2
and spitting out that data, it's identifying occurrences of A$2 and spitting
out the data of the next line below.

Any help would be greatly appreciated.

Ryan

 

[Sean Timmons]

=INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2)

Let's break this formula down.

INDEX looks for the value(s) in the first group:
tests!$C$2:$G$30000

within the columns listed in the 2nd group:
SMALL(IF(tests!$C$2:$C$30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1))

and the rows in the 3rd group:
2

Now, the columns pieces is most difficult so:
SMALL takes the minimum value of the list...

If the values in tests!$C$2:$C$30000 equal the value in A2 THEN
Use row of tests!$C$2:$G$30000, or, 2
ELSE
Use Row of 1:1, or 1.

So, it sounds like you are finding situations where tests!$C$2:$C$30000 does
not equal A2, and thus you are getting Row 1 as your row value in Index, and
since your lookup table starts in row 2, you are getting the value above the
one you want.

Course, a little help would be to know what you are tryign to accomplish...

 

[seed]

I have a sheet of information for tests ('tests' sheet, obviously), with all
data for each test arranged into rows, that my lab performs with the date in
column C, test type in column D, # of samples in E, etc.

A separate sheet ('date'), the one using the formula, is a nicely formatted
sheet that (this is the attempt) sorts the information for all tests on each
separate day. The date is located, for this day at least, in A2. What I'm
trying to do is pull all of the information from 'tests' column C,D,E, and F
that coincides with multiple occurrences of the value given in 'date' A2.

What this formula is giving me is the row below the row that I want.

Seems like kind of a pain just to make a nicely formatted daily schedule,
but from there I can use the 'date' info to automatically fill in all of the
data that is normally entered manually into our scheduling sheet.

SO! It looks like simply changing the range to beginning at C1 rather than
C2 has fixed the problem, though I still can't say that I completely
understand why. Thank you very much Sean.

 

[Sean Timmons]

Sometimes it's easier to just accept that things work.

You can always click the fsubx next to your formula bar and click your way
through the formula. That's IF you want to know the answer.