If, iserror, index, row

[arossijr]

I have another formula that is sorta working... I have it in another Workbook and it works great.. All i changed was the Table and Header info and it works but is a little wonked...

=IF(ISERROR(INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData[Store '#])),ROW(1:1)),2)),"",INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData[Store '#])),ROW(1:1)),2))

An it is entered as an Array formula...
In the other workbook it works great!! But in this one... It is not taking the very first line it should be and it is grabbing a line 1 below the what it should be...

So it is should be grabbing 1,2,3,4,5 it is grabbing 2,3,4,5,6

It is practically a copy paste from the other book, actually it is... then i changed the tables and headers and the =$B$1 ...

Im going knucking crazy...
again please tell me were where i goofed...

 

[arossijr]

Re: IF, ISERROR, INDEX, ROW

Aladin,

I am remote at the moment, I will post the new formula that i was able to get to work taking some of what you had, well sorta... I used +Min and -4 ...

 

[arossijr]

Re: IF, ISERROR, INDEX, ROW

See: https://www.dropbox.com/s/1ujvwxkalw6t05g/arossijr sublist from a Table.xlsx?dl=0

Aladin,

Here is the formula that I am using now: =IF(ISERROR(INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)+MIN(ROW(DRMRwdData)-4)),ROW(1:1)),2)),"",INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)+MIN(ROW(DRMRwdData)-4)),ROW(1:1)),2))

Of course entered in as an Array Formula... I used some of your formula to double check to make sure that it was counting and pulling the correct amount of data.

Thanks Again!
Tony

 

[Aladin Akyurek]

Re: IF, ISERROR, INDEX, ROW

Aladin,

Here is the formula that I am using now: =IF(ISERROR(INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)+MIN(ROW(DRMRwdData)-4)),ROW(1:1)),2)),"",INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)+MIN(ROW(DRMRwdData)-4)),ROW(1:1)),2))

Of course entered in as an Array Formula... I used some of your formula to double check to make sure that it was counting and pulling the correct amount of data.

Thanks Again!
Tony

I'd suggest that you use I suggested as is...