How to use an array formula to omit data if criteria met
Requirement:
The worksheet "Charts" lists and filters supplier details. The charts sheet is shown below:
The user is trying to implement a further filter in the array formula below:
The filter should omit any records that have the same details as selected in "Charts(B3) and listed in "Store Details.column(D)"
i.e. Sub Suppliers 5 and 6 are located in the South West, so they should not be listed.
{=IFERROR(INDEX('Store Details'!$C$3:$C$15,SMALL(IF('Store Details'!$F$3:$F$15=B$5,ROW('Store Details'!$C$3:$C$15)-ROW('Store Details'!E$3)+1),ROWS($B$6:B6))),"")}
Store details sheet is shown below:
Solution:
=IFERROR(INDEX('Store Details'!$C$3:$C$15,SMALL(IF('Store Details'!$F$3:$F$15=B$5,IF('Store Details'!$D$3:$D$15<>$B$3,ROW('Store Details'!$C$3:$C$15)-ROW('Store Details'!E$3)+1)),ROWS($B$6:B6))),"")
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to take a piece of spreadsheet to a sniped code and translate into array form |
| How to insert VLOOKUP into cell with variable array |
| How to use SumProduct array formula |
| How to combine LARGE and SUMIF - Array formula |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
