M
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
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