CODEWITHSUNDEEP
excelexcel-formulavlookup
ryan
ryan

Reputation: 89

Looping Vlookup with INDEX

I am trying to write an excel function/combination of functions that will loop without the use of macros.

I have one table with two columns and another table with 4 columns. The only important columns are the first two.

Table 1

Table 2

I need a function that searches through the first column of the top table and finds all materials on line 51. Material codes that are on rows with line numbers that match 51 will then be placed into the line 51 table below the first table.

NB: the first table (the one with data) will most likely never be sorted so I can not make my life easier via sorting... This first table will also be changing as it is fed from an add in excel program. I must not use macros/VBA as it needs to be sustainable for the average excel user to comprehend. I've tried nested IFs inside VLOOKUPS and many combinations of formulas, I am thinking INDEX is the way to go but I can't find a way to use INDEX to reach my desired goal.

**Even though the Line column contains some multi-lines ex 8/9, these will be non-factors as my formula should only look for a specific line, 51.

Upvotes: 0

Views: 209

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

This formula should work:

=IFERROR(INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-1)/(ISNUMBER(SEARCH(G2,$A$2:$A$6))),ROW(1:1))),"")

It is an array formula so limit the reference range to the extents of the dataset.

enter image description here

Upvotes: 4

Related Questions