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.
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.