Prev: Change the black selected-cell-border to another color (cursor)
Next: Saving from 2007 to 2003 - 2003 won't open the file
From: Jeanette on 2 Feb 2010 09:34
I am trying to use the index and match function to pull data from one table
into another. If I had the table below (which is A1:F7) does anyone know a
formula that could look at coloumn "F" find all rows that have the "30" and
pull info from column "B" into a specified cell (J1). When I use
=index(a1:f7,match("30",f1:f7,0),1) only price (B2) is correctly pulled into
J1. if I want to pull information into J1:J7 a get a repeat of price (B2)
and not volume and sales (B3 and B5)

item 1 2 3 4
a price 50 60 20 30
a volume 10 15 12 30
a uplift 18 2 565 12
b sales 41 10 350 30
b volume 44 65 49 51
b uplift 85 53 3 4

can anyone help?
--
Thanks, Jeanette
From: Tom on 2 Feb 2010 09:50
Assuming your data starts in A1, enter this formula:

=IF(ISERROR(INDEX($B$1:$B$7,SMALL(IF($E$1:$E$7=30,ROW($E$1:$E$7)),ROW(1:1)))),"",INDEX($B$1:$B$7,SMALL(IF($E$1:$E$7=30,ROW($E$1:$E$7)),ROW(1:1))))

Enter it as an array formula, using Conrol Shift and Enter. Copy down as
far as your data goes.
 | 
Pages: 1
Prev: Change the black selected-cell-border to another color (cursor)
Next: Saving from 2007 to 2003 - 2003 won't open the file