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.