Exclude rows of data from a lookup

Hi and welcome to the forum!

Assuming table one is in Sheet1 A1:F4 (with headers in column A), this array** formula, copied down and across to cover two columns will give you the desired ID and REF returns:

=IFERROR(INDEX(Sheet1!$B$1:$F$4,COLUMNS($A:A),SMALL(IF(Sheet1!$B$4:$F$4<>"Completed",COLUMN(Sheet1!$B$4:$F$4)-MIN(COLUMN(Sheet1!$B$4:$F$4))+1),ROWS($1:1))),"")

It would work for the Status as well, though I notice that you're not wanting to return the Assigned Date, so the formula in your third column will need to be:

=IFERROR(INDEX(Sheet1!$B$1:$F$4,COLUMNS($A:D),SMALL(IF(Sheet1!$B$4:$F$4<>"Completed",COLUMN(Sheet1!$B$4:$F$4)-MIN(COLUMN(Sheet1!$B$4:$F$4))+1),ROWS($1:1))),"")

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Why did you decide to omit the column references when you translated my formula? For example:

Projects!$1:$8

and not (guessing):

Projects!$A$1:$E$8

Are you aware of the significance of this in Excel? And also of the potential difference in results it may cause?

Regards

Firstly, have you actually corrected the references now? If so, were the results what you expected?

Secondly, referencing entire columns or rows in array formulas is not advisable (and certainly not an entire sheet!) Surely you can some up with some suitable upper limits for the row and column of the data range? Or, if you need the ranges to be dynamic, we can arrange that as well.

Regards

Ok. The best thing to do would be to set up your range dynamically, so that it responds to additions/deletions in the dataset, and determines the last-used row and column within that range. This negates the need for you to set arbitrarily large range references and also maximises the formula efficiency.

Let me know if you're interested - though for this it would be much easier if a sample workbook were available.

Regards

Sure. Looking at your sample, am I correct in thinking that it will only be the columns that will change? And that the rows will always be fixed, i.e. 1 to 8 in this example, with the Status in the very last of these?

Regards

Hi,

If you go to Name Manager and create a new name, Project_Data say, and enter this in the Refers to: box:

=Projects!$A$1:INDEX(Projects!$8:$8,,MATCH(REPT("z",255),Projects!$1:$1))

Exit Name Manager.

As per the attached, the formula in B2 now becomes:

=IFERROR(INDEX(Project_Data,COLUMNS($B:B),SMALL(IF(INDEX(Project_Data,8,)<>"Completed",COLUMN(Project_Data)-MIN(COLUMN(Project_Data))+1),ROWS($1:2))),"")

Notes

Once you've set up the Named Range, then (unless you've maximised the Name Manager window) highlighting the Refers to: part will put borders around the appropriate range in the sheet. This should currently be equal to A1:F8 in your attachment, and will expand/contract as you add/delete new columns (please check).

The parts you may need to amend to suit your actual spreadsheet are:

Projects!$A$1 should be the upper-left cell in the range.
Projects!$1:$1 and Projects!$8:$8 refer to the first and last rows in that range respectively.

The formula also depends upon the entries in the top row (i.e. Company 1, Company 2, ...etc.) being textual, and may fail if they are in fact numeric (though I can't imagine this being the case).

Hope that helps. Let me know if you need any more assistance.

Regards

You're welcome! And best of luck with the project.

Regards