Searching for unknown text string in a range and then matching it to a reference table

I'll get back to you later this afternoon (Sun, June 7) when I have more time.

Sorry about the delayed response! I got side tracked!

Here goes...

=IFERROR(INDEX($B3:$E3,AGGREGATE(15,6,1/($B3:$E3<>"")*COLUMN($B3:$E3),COLUMNS($G3:G3))-COLUMN($B3)+1),"")

We're telling Excel we want to look in this range for some data:

INDEX($B3:$E3

Since the range is a 1 dimensional array (a single row or column) the data is located by its relative position within the range.

B3 = position 1
C3 = position 2
D3 = position 3
E3 = position 4

We want to extract the data from the cells that are not empty. The data we want to find is defined by this criteria:

1/($B3:$E3<>"")*COLUMN($B3:$E3)

<>"" means "is not blank"

This expression will return a Boolean value of either TRUE or FALSE:

$B3:$E3<>""

Data Range

	B	C	D	E
1
2
3	Red		Blue
4	------	------	------	------

B3 <>"" = TRUE
C3 <>"" = FALSE
D3 <>"" = TRUE
E3 <>"" = FALSE

This result is then used in a division operation:

1/TRUE = 1
1/FALSE = #DIV/0!

B3: 1/TRUE = 1
C3: 1/FALSE = #DIV/0!
D3: 1/TRUE = 1
E3: 1/FALSE = #DIV/0!

This result is then multiplied by the absolute column numbers of the range:

B3: 1 * COLUMN(B3) = 2
C3: #DIV/0! * COLUMN(C3) = #DIV/0!
D3: 1 * COLUMN(D3) = 4
E3: #DIV/0! * COLUMN(E3) = #DIV/0!

Now we have this array: {2,#DIV/0!,4,#DIV/0!}

The AGGREGATE function has many applications. In this application we're using it to return the column numbers of the cells in the range B3:E3 that are not empty.

AGGREGATE(15

15 means return the smallest number

The function has an option that allows it to ignore error values:

AGGREGATE(15,6

6 means ignore error values

So, we can take advantage of this feature by intentionally generating errors for items that we want to ignore. In this case, cells that are empty. That's why we use the division operation in this expression:

1/($B3:$E3<>"")*COLUMN($B3:$E3)

Now we have the absolute column numbers of the cells that are not empty:

{2,#DIV/0!,4,#DIV/0!}

However, as noted above the INDEX function uses relative position numbers so we have to convert the absolute column numbers to relative position numbers. We do that with this expression:

-COLUMN($B3)+1

B3: 2 - COLUMN($B3) + 1 = 1
C3: #DIV/0! - COLUMN($B3) + 1 = #DIV/0!
D3: 4 - COLUMN($B3) + 1 = 3
E3: #DIV/0! - COLUMN($B3) + 1 = #DIV/0!

As we copy the formula across the row we use this expression to increment the argument to return the nth smallest position number:

COLUMNS($G3:G3)

If we enter the first formula in cell G3 then:

COLUMNS($G3:G3) = 1 = return the 1st smallest position number
COLUMNS($G3:H3) = 2 = return the 2nd smallest position number
COLUMNS($G3:I3) = 3 = return the 3rd smallest position number
COLUMNS($G3:J3) = 4 = return the 4th smallest position number

These numbers are then passed to the INDEX function telling it to return the value of the nth position.

If the formula is copied to more cells than there are cells in the data range that aren't empty then it will return an error. So we use the IFERROR function to suppress those errors and return a blank instead.

Hopefully that makes some sense!