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

Hi everyone,

First time poster. I am hoping you folks can kindly help me with a very complicated set of questions. I have been trying to look this up for the last two days and I cannot seem to figure out a way to do it.

This post has been revised due to breadth of the question.

Essentially - I want to be able to have a function that searches the third row in column A for a text string because only the suspected diagnosis will be filled in, and the other cells will be blank. The problem is that I do not know what string I am searching for! It could be A, B, C, or A and B, etc. So how do I get excel to search a range of cells for to see if ISTEXT=true and then return to me what the value of the text was so I know what I am dealing with.

Please see below for a revised question. Find my excel sample worksheet attached Colors.xlsx. Thank you!

Originally Posted by the_nibs

if I have the following cells: <red> <blank> <blue> <blank> <blank> is there a way to search those cells to give me the result 'red' 'blue'?

Data Range

	A	B	C	D
1	Red		Red	Red, Blue
2			Blue
3	Blue
4
5	------	------	------	------

If you want the results in separate cells like in col C then we can use a formula to do it.

If you want the results to be a concatenated string like in col D then you'll need to use a VBA function to do it.

Which method do you want to use?

Colors.xlsxIn separate cells would work. My raw data is transposed though. Sorry, I don't know how to make that table you just showed but my data shows up like this:

A....B....C....D....E
red..<>...<>..blue..<>
<>..grey..<>...<>..<>
<>..<>...green..<>..<>
<>..grey...<>...<>..pink

Is there a way to make it show up 'red' and 'blue' for the first row, 'grey' for the second, 'green' for the third, 'grey' and 'pink' for the fourth row, etc?

Thanks!

Try this...

This formula entered in G3:

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

Copy down to G9 then across until you get a column full of blanks.

AGGREGATE is a new function that was introduced in Excel 2010.

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!

Originally Posted by the_nibs

Tony, thank you so much for taking the time to explain that. I know it probably took you a while to type that.

Yes, it did. I'm a one finger typer!

I think I follow everything up until the '-COLUMN($B3)+1' - why not simply subtract 1 i.e. 1/($B3:$E3<>"")*COLUMN($B3:$E3)-1 as we know the absolute position (column b = 2) is always 1 greater than the relative position of the first cell so column b = 2-1 = 1, c = 3-1 = 2, etc.

Not necessarily. Make the data range F5:I5 with 1/($F5:$I5<>"")*COLUMN($F5:$I5)-1

See what results you get.

oh, i see. the columns($G3:G3) is acting as the constant for the k-th smallest number for the aggregate function

Yes!