Extract all values in a range

Could you please explain why copy and paste would not be a suitable solution?

So it's simply reading the data across, is it? When you say values change, what exactly do you mean? Will the list grow? Will rows sometimes be deleted from anywhere in the range? All of these things will affect any solution offered.

Enter this array formula in cell A4 and copy across and then down

Formula:

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

OK, then the formula provided in post #5 will not work for you - I have tested it by deleting a row from the source data, and it fails at that point. I am sure that AlKey will have a better idea - I'll have a think about it, too.

Please do not send PMs to members pestering them for help - this is in breach of forum rule #4:

4. Don't Private Message or email Excel questions to moderators or other members. (or Word, Access, etc.) The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

I am going offline now for the evening. I am sure someone will be able to help. I shall be back online tomorrow morning.

Thanks. Hope you get a solution!

Originally Posted by AliGW

OK, then the formula provided in post #5 will not work for you - I have tested it by deleting a row from the source data, and it fails at that point.

The only reason it fails upon row deletions is because AlKey used:

ROW($A$7:$A$22)

rather than the more robust:

ROW(Products!$A$7:$A$22)

Regards

Originally Posted by bjnockle

XOR LX: great...works. Can this also start from Products!A7:A22 instead of Products!A:A? Would like it to start from Products!A7:A22

No. You misunderstand. It is currently calculating over rows 7-22 only.

Although this is not the only method, AlKey's decision to use Products!A:A as the array to pass to INDEX is a good one, since this way we reduce the construction required to pass as the row_num parameter.

Regards

Originally Posted by bjnockle

XOR LX: Did not get it to work on my end. Products!A:A is making it pull data from the first row instead of row 7. can you help with the index approach? Thanks

But we're referring to the file AlKey posted, correct?

So only the reference immediately after the INDEX should be to a full column, i.e. Products!A:A. The others, after IF, ROW, etc., need to reference explicitly the required range, e.g. Products!$A$7:$A$22.

But this is all just so in AlKey's attached file, so not sure where the confusion could have arisen?

The only time I'd avoid the method given by AlKey is if you were actually using an Excel Table, in which case it's better to consistently reference the same range. However, this appears to not be the case here.

Regards

??

Did you read my last post?

Originally Posted by XOR LX

So only the reference immediately after the INDEX should be to a full column, i.e. Products!A:A. The others, after IF, ROW, etc., need to reference explicitly the required range, e.g. Products!$A$7:$A$22.

Regards

when I changed it to
=IFERROR(INDEX(Products!$A$7:$A$21,SMALL(IF((Products!$A$7:$A$21=Products!$A$2)+(Products!$A$7:$A$21=Products!$A$3)+(Products!$A$7:$A$21=Products!$A$4),ROW(Products!$A$7:$A$21)),ROWS(A$4:A4))),"")

Don't change the first reference after the INDEX function - leave it as Products!A:A.

Originally Posted by bjnockle

=IFERROR(INDEX(Products!$A$7:$A$21,SMALL(IF((Products!$A$7:$A$21=Products!$A$2)+(Products!$A$7:$A$21=Products!$A$3)+(Products!$A$7:$A$21=Products!$A$4),ROW(Products!$A$7:$A$21)),ROWS(A$4:A4))),"")

I haven't downloaded any files but here's how I would write that formula...

=IFERROR(INDEX(Products!A:A,SMALL(IF(ISNUMBER(MATCH(Products!$A$7:$A$21,Products!$A$2:$A$4,0)),ROW(Products!$A$7:$A$21)),ROWS(A$4:A4))),"")

Still array entered.

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

You're welcome!

You're welcome. Thanks for the feedback!

Likewise!

Cheers