Multiple conditions in array formula

[Letto4135]

I'm working on a very advance database and need to look at different instances of a column I've simplified it as much as I can using helper columns but still need at least one column of array formulas, but I can't figure out how to write it correctly.

I'm not near my computer so this won't be the exact formula but it is something like

=IFERROR(INDEX(C:C,MATCH(SMALL(D:D="X",ROW(D:D)-ROW(D:D,1)),1,1)),""

Which works, but not sure if that's the actual formula I'm using..

I need one that continues on the if error to look at another column for the X instead so something like

=IFERROR(INDEX(C:C,MATCH(SMALL(D:D="X",ROW(D:D)-ROW(D:D,1)),1,1)),INDEX(C:C,MATCH(SMALL(E:E="X",ROW(E:E)-ROW(E:E,1)),1,1))

Which doesn't work, it still inputs the answer from the first portion of the formula...
It should look in D:D for x and input what is in that row in C:C and if it can't find it look in E:E for an x and input from that row instead.

 

[FDibbins]

Based on your 1st example, that formula doesn't look right...
INDEX(C:C,MATCH(SMALL(E:E="X",ROW(E:E)-ROW(E:E,1)),1,1))
maybe missing an IF in there somewhere?
INDEX(C:C,MATCH(SMALL(if(E:E="X",ROW(E:E)-ROW(E:E,1)),1,1))

It would probably be better if you gave more detail on what you want, and when you are working with.
Also, avoid using full-column references inside an ARRAY formula (which that is), it can slow your file down

 

[Letto4135]

Well, like I said that's not the exact formula that I'm using, I'm not near my computer, I went off of what I could remember of the formula. And I don't have the actual formula looking at the whole column it's looking at CD3:CD2000 and D3:D2000 which is as small as I can make it.. The formula I actually have works, I just need to add on to it somehow like I said in the original post, I made it as specific to what I need as I can the only thing I can correct is showing the exact formula instead of paraphrasing..

This database has several hundred array formulas and it doesn't slow it down at all in excel 2016 so far thankfully. I've tried everything I can to avoid using them but this thing is so complex it has to sometimes.

Based on your 1st example, that formula doesn't look right...
INDEX(C:C,MATCH(SMALL(E:E="X",ROW(E:E)-ROW(E:E,1)),1,1))
maybe missing an IF in there somewhere?
INDEX(C:C,MATCH(SMALL(if(E:E="X",ROW(E:E)-ROW(E:E,1)),1,1))

It would probably be better if you gave more detail on what you want, and when you are working with.
Also, avoid using full-column references inside an ARRAY formula (which that is), it can slow your file down

 

[Letto4135]

My orignial formula is
=IFERROR(INDEX(D3:D2000,SMALL(IF(BF3:BF2000="X",ROW(BF3:BF2000)-ROW(INDEX(BF3:BF2000,1,1))+1),1))

But I need to continue on the if error like this

=IFERROR(INDEX(D3:D2000,SMALL(IF(BF3:BF2000="X",ROW(BF3:BF2000)-ROW(INDEX(BF3:BF2000,1,1))+1),1)),IFERROR(INDEX(D3:D2000,SMALL(IF(BE3:BE2000="X",ROW(BE3:BE2000)-ROW(INDEX(BE3:BE2000,1,1))+1),1)),""))

But that doesnt work, it still inputs the first instance even when it cant find the X.

 

[Letto4135]

JK I fixed it.
=IF(BG1="ff",INDEX(D3:D2000,SMALL(IF(BE3:BE2000="X",ROW(BE3:BE2000)-ROW(INDEX(BE3:BE2000,1,1))+1),1)),IF(BG1="ab",INDEX(D3:D2000,SMALL(IF(BF3:BF2000="X",ROW(BF3:BF2000)-ROW(INDEX(BF3:BF2000,1,1))+1),1)),IF(BG1="tb",INDEX(D3:D2000,SMALL(IF(BD3:BE2000="X",ROW(BD3:BE2000)-ROW(INDEX(BD3:BE2000,1,1))+1),1)))))