Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Index() or Vlookup()
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:D,MATCH("b*",Import!D: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: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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Index() or Vlookup()
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,R OWS(H$2:H2))),"") Copy down until you get blanks meaning the data has been exhausted. -- Biff Microsoft Excel MVP "Frank Pytel" wrote in message ... 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:D,MATCH("b*",Import!D: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: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP or maybe INDEX | Excel Worksheet Functions | |||
VLOOKUP, INDEX, or ....? | Excel Worksheet Functions | |||
Dynamic Range Using INDEX instead of Offset | Excel Discussion (Misc queries) | |||
dynamic table_array in lookup,match & index | Excel Worksheet Functions | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions |