Dynamic Index() or Vlookup()

[Frank Pytel]

Hello All;

--All results are in row 2 for this question.--

I am trying to perform a vlookup()-ish or index()-ish function on text that
is imported using the following formula.

=INDEX(Import!D,MATCH("b*",Import!D,0))

The Import tab is drawn from another workbook. I am hoping to filter down
the list of imported information based on a selection criteria. I only want
Beams, Girders and other Horizontal members. They are coded with the letter
"B" and a series of numbers (B###).

I have another formula that gives me the cell address for the corresponding
values that I get with the above function. The address formula follows.

=ADDRESS(MATCH("B*",Import!D,0),4,4)

I increment the above formulas to move down to the next row to avoid using
an array formula.

My problem lies in performing the vlookup() or index() on the remainder of
the information.

If cell B2 contains "B1" (Beam 1) I might also find "B1" at cell B12, B15,
B16, B1000, etc. While "B1" shoul be the same size, shape and length Beam as
all "B1"'s, There is other information that I need to get that is unique to
each individual Beam.

Can anyone help me with this. Don was a huge help with the first formula and
that led me to the others. All help is greatly appreciated.

Thanks and God Bless

Frank Pytel

 

[T. Valko]

If you don't want to use array formulas then you're going to need to use a
helper column that "marks" the rows of data you're interested in.

Let's use this example data to demonstrate this:

...........D..........E
1....header...header
2......B1.........x.....
3......F5.........o.....
4......B4.........t.....
5......H7........y.....

You want to extract data from column E where column D starts with a "B".

Use column F as the helper column to identify those rows of interest. Enter
this formula in F2 and copy down to F5:

=IF(LEFT(D2)="B",ROW(),"")

Now, setup the extraction formula. Assume you want the data extracted to
column H starting in H2. Enter this formula in H2:

=IF(ROWS(H$2:H2)<=COUNT(F:F),INDEX(E:E,SMALL(F:F,ROWS(H$2:H2))),"")

Copy down until you get blanks meaning the data has been exhausted.