I have a spreadsheet where I've done an index match to return
criteria. I have to match three or four columns to get the information
that I need, so I have three or 4 columns that either have the
information I need or a "NO" from the index match.
I need a formula to look at the columns and say, if the value is in
this column, return it, otherwise go to the next column, etc.
also I have the formula
IF(ISERROR(SEARCH("FL:",IR3)),"",IR3)
but where the "FL:" is, may be "FL:ABC" or "FL:XYZ", with many
combinations.
so I need to match the 3 or 4 columns, and return the value that
contains "FL:" with something else after it.
I hope this makes sense.
I posted this in a forum, but I have searched and cannot find it. Our
server went down at the same time, so I am not certain it posted.
Thank you
Plus766

On Sep 28, 12:21*pm, plus766 wrote:
I have a spreadsheet where I've done an index match to return
criteria. I have to match three or four columns to get the information
that I need, so I have three or 4 columns that either have the
information I need or a "NO" from the index match.
I need a formula to look at the columns and say, if the value is in
this column, return it, otherwise go to the next column, etc.
also I have the *formula
IF(ISERROR(SEARCH("FL:",IR3)),"",IR3)
but where the "FL:" is, may be "FL:ABC" or "FL:XYZ", with many
combinations.
so I need to match the 3 or 4 columns, and return the value that
contains "FL:" with something else after it.
I hope this makes sense.
I posted this in a forum, but I have searched and cannot find it. Our
server went down at the same time, so I am not certain it posted.
Thank you
Plus766

I'm not entirely sure I follow, but...

I think what you're saying is that you've used the INDEX function with
one or more nested MATCH() functions to return values that you have in
a data array.

Next, your INDEX w/ nested MATCH statement(s) has been applied to 3 or
4 columns/cells that return either the data array value or the string
"NO".

Furthermore, you need to test the results of each INDEX/MATCH usage
(in a particular row) to determine whether certain text appears in the
value.

If it appears, you need to parse only the text string you're looking
for, then append it with "something else".

If my understanding is correct, everything appears to be working up
until you're doing the test for the desired text string.

First, I'm not aware of an Excel SEARCH() function. You may mean the
FIND() function.

I can think of a couple of ways to do what I belive you're describing.

e.g., if your test string is "FL:" & it's located in cell A2 and cells
B2, C2, D2, & E2 contain your various INDEX/MATCH functions, you could
concatenante all returned values from your INDEX/MATCH functions into
a single cell. F2 formula =B2&C2&D2&E2.

Then you could apply a function to test for the presence of the test
string within the concatenation and return the test string (not the
data array value returned by the INDEX/MATCH function). Finally, you
could append that with your "something else" string.
e.g. G2 formula = IF(NOT(ISERROR(FIND(A2,F2))),A2,"")&"something else"

Bartt,

you can find details of the Excel SEARCH function in XL Help. It is
similar to FIND, but it is not case-sensitive and wildcards can be
used in the find_text parameter. You could use it like this:

=IF(ISNUMBER(SEARCH("FL:"&"*",IR3)),IR3,"")

Hope this helps.

Pete

On Oct 1, 6:43*pm, Bartt wrote:

I'm not entirely sure I follow, but...


First, I'm not aware of an Excel SEARCH() function. *You may mean the
FIND() function.