Issue with returning proper row number using =Small Function

Hello,

I've hit a roadblock. I'm trying to use the =Small function to retrieve a number of results to populate a column. However there are multiple criteria. I tried following the MS office tutorial here: http://office.microsoft.com/en-us/ex...001226038.aspx

It helped somewhat, but I'm having a hard time finding out why it isn't working.

Here is my single variable formula: =SMALL(IF(Indoor_Make=$C&86,ROW(Indoor_Make)),ROW(1:1)) +shift +enter Where "Indoor_Make" is an array that is 1 column on a separate sheet.
The above formula is pasted down a blank column until I get an error.

Here is my double variable formula: =SMALL(IF(COUNTIFS($C$86,Indoor_Make,$D$86,Indoor_Zone),ROW(Indoor_Make)),ROW(1:1)) +shift +enter Where "Indoor_Make" and "Indoor_Zone" are both arrays that are columns on another sheet.
The above formula is pasted down a blank column until I get an error

Both of the above work in returning the correct row numbers for the criteria when compared with the data on the separate sheet. The double variable formula works, and returns the row numbers that match both criteria.

Here's where I run into a problem. I try using the tutorial's formula for displaying the data in the matching rows
Here is my formula: =INDEX('Indoor Unit Details'!$A$1:$R$500,SMALL(IF(COUNTIFS(Indoor_Make,Dashboard!$C$86,Indoor_Zone,Dashboard!$D$86),ROW(Indoor_Make)),ROW(1:1)),3) +shift +enter Where 'Indoor Unit Details'!$A$1:$R$500 is the table I'm referencing on the separate sheet and I want to return the results in Column 3 that match the rows that fit both criteria.

The problem is, it isn't working. It displays what is in column 3, but it ignores the criteria and just displays everything. What am I doing wrong?

Many Thanks and Best Regards,

Sard

Originally Posted by Sardaukar

Here is my formula:

=INDEX('Indoor Unit Details'!$A$1:$R$500,SMALL(IF(COUNTIFS(Indoor_Make,Dashboard!$C$86,Indoor_Zone,Dashboard!$D$86),ROW(Indoor_Make)),ROW(1:1)),3)

+shift +enter

The correct key combination to array enter a formula is CTRL + SHIFT + ENTER.

What is the cell address of the first cell the formula is entered in? We will use that cell addrsss to replace this expression:

ROW(1:1)

Using ROW(1:1) leaves the formula vulnerable to new row insertions which can lead to incorrect results.

Originally Posted by Sardaukar

How do you replace the ROW(1:1) expression? ROW(F157) or just F157?

Like this...

Array entered** in cell F157:

=INDEX('Indoor Unit Details'!C:C,SMALL(IF(COUNTIFS(Indoor_Make,Dashboard!$C$86,Indoor_Zone,Dashboard!$D$86),ROW(Indoor_Make)),ROWS(F$157:F157)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER

The problem is that your named ranges:

Indoor_Make
Indoor_Zone

Refer to an external file: AC Workbook.xlsx

It doesn't look like you want to reference an external file. It looks to me like you're wanting to extract data (BTU/h) from the Indoor Unit Details sheet for the Make and Multizone entered on the Dashboard sheet.

Is that correct?

hi Sardaukar. first, press CTRL + F3 & adjust the 2 Named Range. it's now reading from another file called "AC Workbook" instead of the existing one. so remove everything in the formula in red. mine is something like that, yours would be different:
'C:\Users\Admin\Downloads\[AC Workbook.xlsx]Indoor Unit Details'!$A$2:$A$500

then change your range of either one of them. they are supposed to reference the same number of rows:
'Indoor Unit Details'!$A$2:$A$500
'Indoor Unit Details'!$F$2:$F$507

C21 & below are also not supposed to appear, so maybe:

Formula:

Please Login or Register  to view this content.

OK, try it like this...

If you want the BTU/h data...

On the Dashboard sheet add the column header BTU/h in cell C1.

Then, enter this array formula in C2:

=IFERROR(INDEX('Indoor Unit Details'!C:C,SMALL(IF('Indoor Unit Details'!A$2:A$85=A$2,IF('Indoor Unit Details'!F$2:F$85=B$2,ROW('Indoor Unit Details'!F$2:F$85))),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

There are many duplicates there with no other identifying data so I'm guessing you want additional data to be returned?

You're welcome. Thanks for the feedback!