Return multiple values with multiple criteria and array formula

[mtinode]

I have this formula and it works great. But now I find that I need a 2nd criteria to keep from getting duplicate rows. I need for 'Raw NimSoft'!F:F="*Client" in addition to the 'Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB" already in the formula.

Here is my working formula. It simply returns everything in column H (the 7th column in my index) where that text appears in column D. If you need the data I can provide it, but it's just text. I'm only concerned with columns D, F and H.

=LEFT(IF(ISERROR(INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(2:2)),7)),"",INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(2:2)),7)),LEN(IF(ISERROR(INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(2:2)),7)),"",INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(2:2)),7)))-9)

Entered with CTRL-SHIFT-ENTER as an array formula.

Thanks for any help with this.

 

[MarcelBeug]

Looks to me like a highly inefficient formula returning the value from column H (less 9 right most characters) from the row housing the 2nd occurrence of QOS_VMWARE_DATASTORE_CAPACITY_GB in column D.

What Excel version are you using?

The additional criterium means you are looking for "Client" in the 6 right most characters in column F?

For a more efficient solution: is it correct that there are no gaps in column B of 'Raw Nimsoft', e.g. if there are 2000 values it means these are in row 1:2000?

 

[mtinode]

My Excel version is 2010. No gaps in column B, that is correct. And yes the string in Column F that I need ends in "Client". But it should be pulling every row where QOS_VMWARE_DATASTORE_CAPACITY_GB, but I see I posted from the 2nd row of my table instead of the first. Here is that cell from the first row:

=LEFT(IF(ISERROR(INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(1:1)),7)),"",INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(1:1)),7)),LEN(IF(ISERROR(INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(1:1)),7)),"",INDEX('Raw NimSoft'!$B$1:$H$2800,SMALL(IF('Raw NimSoft'!D:D="QOS_VMWARE_DATASTORE_CAPACITY_GB",ROW('Raw NimSoft'!D:D)),ROW(1:1)),7)))-9)

I suspect I need to use Match(), but have no idea how that affects the rest of the formula I was able to steal and modify to get to this point.

 

[MarcelBeug]

On tab Formulas, Name Manager, define names:

[B]DataD[/B]: ='Raw Nimsoft'!$D$1:INDEX('Raw Nimsoft'!$D:$D,COUNTA('Raw Nimsoft'!$B:$B))
[B]DataF[/B]: ='Raw Nimsoft'!$F$1:INDEX('Raw Nimsoft'!$F:$F,COUNTA('Raw Nimsoft'!$B:$B))
[B]DataH[/B]: ='Raw Nimsoft'!$H$1:INDEX('Raw Nimsoft'!$H:$H,COUNTA('Raw Nimsoft'!$B:$B))

Suppose your formula resides in C1 and down, array formula, confirm with Ctrl+Shift+Enter and copy down

=IFERROR(LEFT(INDEX(DataH,SMALL(IF(DataD="QOS_VMWARE_DATASTORE_CAPACITY_GB",IF(RIGHT(DataF,6)="Client",ROW(DataH))),ROWS(C$1:C1))),LEN(DataH)-9),"")

First this formula is much more efficient and second it can be copied down.