Have "IF,Then" to get data from multiple columns--can I nest add'l IF,THENs?

[marlea]

When applied to the example data below, this formula will retrieve th
data in columns B and C when the value in column A equals 3.

*array entered*
=IF(ISERROR(INDEX(Sheet1!$A$1:$C$6,SMALL(IF(Sheet1!$A$1:$A$6=3,ROW(Sheet1!$A$1:$A$6)),ROW(1:1)),
{2,3})),"",INDEX(Sheet1!$A$1:$C$6,SMALL(IF(Sheet1!$A$1:$A$6=3,ROW(Sheet1!$A$1:$A$6)),ROW(1:1)),{2,3}))

Data example in Sheet 1:
A............B................. C
3..........aaa.pdf........Apple
2..........ddd.pdf........Dogs
1..........bbb.pdf.......Bubbles
3..........ccc.pdf........Cats
2.........eee.pdf........Elephants
4.........fff.pdf.........Flowers

Result on Sheet 2 after applying formula:
A..................B
aaa.pdf.........Apple
ccc.pdf.........Cats

Issue: I've been told there probably will be 10 lookup values; i.e.
column A could have values from 1 to 10. How do I account for all o
these values in the formula above? Do I nest or concatenate additiona
IF statements for each of the 10 lookup values? If I knew how t
nest/concatenate one additional IF statement with my formula above,
could do it for the remaining lookup values. Can anyone show me how t
do this? Or is there a better way than using multiple IF statements
Thank you

 

[Biff]

Hi!

You would need a separate formula for each of the lookup values.

In the meantime, you can shorten your current formula to:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!A$1:A$6,3),INDEX(Sheet1!A$1:C$6,SMALL(IF(Sheet1!A$1:A$6=3,ROW($1:$6)),ROW(1:1)),{2,3}),"")

Biff