When applied to the example data below, this formula will retrieve the
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 of
these values in the formula above? Do I nest or concatenate additional
IF statements for each of the 10 lookup values? If I knew how to
nest/concatenate one additional IF statement with my formula above, I
could do it for the remaining lookup values. Can anyone show me how to
do this? Or is there a better way than using multiple IF statements?
Thank you!


--
marlea
------------------------------------------------------------------------
marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209
View this thread: http://www.excelforum.com/showthread...hreadid=400814

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(Sh eet1!A$1:C$6,SMALL(IF(Sheet1!A$1:A$6=3,ROW($1:$6)) ,ROW(1:1)),{2,3}),"")

Biff

"marlea" wrote in
message ...

When applied to the example data below, this formula will retrieve the
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 of
these values in the formula above? Do I nest or concatenate additional
IF statements for each of the 10 lookup values? If I knew how to
nest/concatenate one additional IF statement with my formula above, I
could do it for the remaining lookup values. Can anyone show me how to
do this? Or is there a better way than using multiple IF statements?
Thank you!


--
marlea
------------------------------------------------------------------------
marlea's Profile:
http://www.excelforum.com/member.php...o&userid=26209
View this thread: http://www.excelforum.com/showthread...hreadid=400814