I found a question in the comments section. You can find the question in this post: Lookup values in a range using two or more criteria and return multiple matches in excel

Question: Unfortunately, I can't make enough sense of what happens internally to adapt it exactly.

Here is what the post is about:

How to lookup values in range using two or more criteria and return (if possible) multiple adjacent values using an array formula.

Array formula in B22:

copied down as far as needed.

Named ranges

tbl (B3:G8)
search_tbl (B18:B19)
What is named ranges?

Question: Unfortunately, I can't make enough sense of what happens internally to adapt it exactly.

Answer: Here is an explanation of what happens internally.

To filter specific values in a range I use INDEX().

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Each cell in a given range has a row and column number. See row 2 and column K in the picture below.

To be able to extract the matching values we need to create an array containing row and column numbers.

Upper left cell in range: Row 1 and column 1 = 1,1
Next cell to the right: Row 1 and column 2 = 1,2
Next cell to the right: Cell E1: Row 1 and column 3 = 1,3

But what about row 10 and column 10? 10,10 but that equals 10,1 so excel can't distinguish between row 10, column 10 and row 10, column 1. How do we solve this?

Upper left cell in range: Row 1 and column 1 = 1 + 1/16384
Next cell to the right:Row 1 and column 2 = 1 + 2/16384
Next cell to the right:Row 1 and column 3 = 1 + 3/16384

Maximum columns in excel 2007 is 16384. That is why I use 16384.

Now each cell has a unique number. We can convert this number back to rows and columns. But how do we know what values to convert?

Formula: COUNTIF(search_col, tbl)
returns an array where 1 = TRUE and 0 = False. Compare pictures below.

Now we can create an array formula to extract rows:

IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1)

Sort the array:

SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1))

{ 1, 2, 3, 4}

INDEX(array,row_num,[column_num])

INDEX(tbl,SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)),[column_num])

Next we create an array formula to extract column numbers (sorted by rows).

SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))

{ 1.00029705323194, 2.00005941064639, 3.00017823193916, 4.00029705323194}

Remove row numbers from array:

SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1))

{ 0.00029705323194, 0.00005941064639, 0.00017823193916, 0.00029705323194}

Multiply by 16384 to create column array:

(SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)))*16384

{5, 1, 3, 5}

Finally adding 1 to each number in the array. We want to return the value to the right of each match. South Carolina => Taylor and so on.

(SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)))*16384+1

{6, 2, 4, 6}

INDEX(array,row_num,[column_num])

INDEX(tbl,{ 1, 2, 3, 4},{6, 2, 4, 6}) = {Taylor, Johnson, Miller, Lopez}

Related posts:

Lookup values in a range using two or more criteria and return multiple matches in excel

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

Vlookup with 2 or more lookup criteria and return multiple matches in excel

Lookup a value in a list and return multiple matches in excel

Lookup with multiple criteria and display multiple search results using excel formula