# Thread: returning a row with the smallest number (2000)

1. ## returning a row with the smallest number (2000)

How can I get excel to return a row that contains the smallest number? I tried the DMIN command, however, I don't want the smallest number from each column. Is there a different way to use this command to make it do what I want? If not, is there another way to do this? Attached is an example of exactly what I am trying to do. Thanks and look forward to the responce.

2. ## Re: returning a row with the smallest number (2000)

The row of the spreadsheet can be obtained from the ARRAY (ctrl-shift-enter to confirm):
=MIN(IF(G10:N15=MIN(G10:N15),ROW(G10:N15)))
=13 in the sheet

The row WITHIN the range can be obtained
=MIN(IF(G10:N15=MIN(G10:N15),ROW(G10:N15)))-ROW(G10:N15)+1
=4 (4th row of data range)

G10:N15, could be generalized into a range name if desired.
Steve

3. ## Re: returning a row with the smallest number (2000)

I tried this formula but I keep getting the #value sign. What am I doing wrong? See attachment.
Thanks.

4. ## Re: returning a row with the smallest number (2000)

Select cell
hit F2 key [now in edit mode]
Do not hit enter key!
hold <ctrl>+<shift> then hit <enter> key

Now the formula will work. (that is what i meant by confirm with ctrl-shift-enter). The formula is An ARRAY formula (see help for more info)

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•