Index small if using choice of columns based on cell

**Trax** · 05-24-2013, 03:38 AM

I am looking for help in adjusting an exiting formula

I have the following array formula.

=IFERROR(INDEX('Actuals Input'!$AC$4:$AC$7885,SMALL(IF('Actuals Input'!$AF$4:$AF$7885=$C$52,ROW('Actuals Input'!$C$4:$C$7885)-3),ROW('Actuals Input'!B1))),"")

I want to amend the part that is underlined, so that it choses either column V or X based on the the value of 'Calculations'!B49 being TRUE for column X and FALSE for column V.

**Fotis1991** · 05-24-2013, 03:52 AM

Perhaps a small sample workbook?

**Trax** · 05-24-2013, 04:07 AM

Fotis1991,

Sorry - a sample workbook is going to prove too difficult given its size and the content.

**Fotis1991** · 05-24-2013, 04:27 AM

So in this case the best that i can do is to show you another thread with same formula that i(we) offered a solution for multiply choices.

http://www.excelforum.com/excel-form...condition.html

Good luck.

**Trax** · 05-24-2013, 05:03 AM

Thanks Fotis,

The formula that you offer is essentially the same as what I have - what I am after is:

=IFERROR(INDEX('Actuals Input'!$X$4:$X$7885,SMALL(IF('Actuals Input'!$X$4:$X$7885=$D$52,ROW('Actuals Input'!$C$4:$C$7885)-3),ROW('Actuals Input'!B2))),"")

To change the underlined column from 'X' to 'V' if cell D53 changes from 'Service Code' to 'Activity Code'.

**Fotis1991** · 05-24-2013, 05:14 AM

As i said if i don't have a sample workbook i can not test anything...Just a quess...trying to help..

=if(d52='Service Code',INDEX('Actuals Input'!$X$4:$X$7885,SMALL(IF('Actuals Input'!$X$4:$X$7885=$D$52,ROW('Actuals Input'!$C$4:$C$7885)-3),ROW('Actuals Input'!B2))),if(d52='Activity Code',INDEX('Actuals Input'!$v$4:$v$7885,SMALL(IF('Actuals Input'!$X$4:$X$7885=$D$52,ROW('Actuals Input'!$C$4:$C$7885)-3),ROW('Actuals Input'!B2))))

Of course UNTESTED.