Formula to extract running data.

Copper Contributor

I am using Excel 2010. I have a simple spreadsheet that I am attempting to extract data from. I need a formula in cell E3, E:4, E:5, ect… that will look down column A:A to find a value that starts with the value listed in cell E:1. Once it finds a matching value it will display the value in the corresponding cell in column B:B as the result. I will need to carry the formula over to F:3, F:4, F:5 Ect… displaying the result found in column C:C. The problem I am having is after it displays the first found result it needs to go to the second found result, then the third and so on so that I end up with a list of part numbers for each line in column A:A that starts with the value in E:1. The list in column H:H and I:I is an example of the output I am looking for. Any help would be appreciated.

 

StevenConrad1_0-1640881076715.png

 

4 Replies

@StevenConrad1 

=INDEX($B$1:$B$25,SMALL(IF(LEFT($A$1:$A$25,6)=$F$1,ROW($1:$25)),ROW(E1)))

=INDEX($C$1:$C$25,SMALL(IF(LEFT($A$1:$A$25,6)=$F$1,ROW($1:$25)),ROW(E1)))

 

If your search criteria (e.g. NHWHSE) has 6 digits you can use above formulas as shown in attached example file. Enter formula as arrayformula if you don't work with Office365 or 2021. The formulas can easily be adapted to other ranges and to other number of digits.

@StevenConrad1 

For this

image.png

in E3

=IFERROR(  INDEX(A:A, AGGREGATE(15, 6, 1/( LEFT(A:A, LEN($E$1) ) = $E$1)*( ROW(A:A)- ROW($A$1) + 1), ROW() - ROW($E$2) ) ), "" )

in F3

=IFERROR(  INDEX(B:B, AGGREGATE(15, 6, 1/( LEFT(A:A, LEN($E$1) ) = $E$1)*( ROW(A:A)- ROW($A$1) + 1), ROW() - ROW($E$2) ) ), "" )

and drag them down till empty cells appear. Above are regular formulae for 2010.

@OliverScheurich 

Thank you for the input. However, as you can see in the example, even when changing the references cell, I get the same responses. So it is not pulling out the  data required. I may need to bypass the formula all together and create a separate work book that has the data filtered 100 different times to extract the specific parts for each order.

StevenConrad1_0-1640898377191.png

 

@StevenConrad1 

In the attached file you can select a search value from the dropdown list in cell F1 to display the results dynamically. Is this what you want to do? I adapted the formulas for the search values in cells I1, L1 and O1 and it works as intended in the attached file.