I am trying to capture text in a range of cells using a formula only if the
text in another cell matches the criteria. Here is what I have come up with:


=MB41!B9&":"&B117(IF(MB41!$B$9:$B$120=B5))

Could you tell me what I have wrong?


--
Thank you,
JR

You are approaching the problem the back to front.

What you need is =If(condition1,do this,if not do this).

If you want to meet more than one condition then

=If(and(condition1,condition2,......),do this,if not do this)

Hope this helps

Ed


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=508920

This is what your formual should lóok like for the following example

columns A to F:

a f ab:cd ab cd
b
c
d
e
f
g
h

Formula in C1;
=IF(ISNA(MATCH(B1;A1:A8;0));"";IF(MATCH(B1;A1:A8;0 )0;E1&":"&F1;""))

Adapt the formula to your needs.

Hans

It is saying invalid for the answer.

"MB41" is the Sheet that I am comparing to cell B5 on Sheet "Totals". I
want to compare the text on MB41 B9:B117 to B5. If it matches, then I want
to post the text that is in MB41 A9:A117 into the cell of A5 on Totals. Does
this help you understand what I am wanting to do

This is how I adapted it to yours:

=IF(ISNA(MATCH(B5; MB41!B9:B117;0));"";
IF(MATCH(B5;MB41!b9:B117;0)0;MB41!A1&":"&A117;"") )

Where did I go wrong?
--
Thank you,
JR


"flummi" wrote:

This is what your formual should lóok like for the following example

columns A to F:

a f ab:cd ab cd
b
c
d
e
f
g
h

Formula in C1;
=IF(ISNA(MATCH(B1;A1:A8;0));"";IF(MATCH(B1;A1:A8;0 )0;E1&":"&F1;""))

Adapt the formula to your needs.

Hans

So what you are saying is to do it this way:

=IF(MB41!B9:B120=B5,MB41!A9:A120)

What I am wanting is: If the text in MB41 B9 through B120 is the same as B5
then put the text that is in MB41 A9 through A120 into cell A5 with a slash/
in between. Is this correct
--
Thank you,
JR


"EdMac" wrote:


You are approaching the problem the back to front.

What you need is =If(condition1,do this,if not do this).

If you want to meet more than one condition then

=If(and(condition1,condition2,......),do this,if not do this)

Hope this helps

Ed


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=508920

Okay, I tried to map your situation on my machine.

Here's the range on MB41:

1 red
2 green
3 magenta
4 brown
5 yellow
6 pink
7 black
8 white
9 mixed

Here's the range on Totals (including the cell B5)

pink



6
6
6
6
6
6
6
6
6

Here's the formula in A9 on Totals (copied down to A17). You may need
to replace the colons with commas depending on your local Windows
settings for list separator.

=IF(ISNA(MATCH($B$5;MB41!$B$9:$B$17;0));"";OFFSET( MB41!$A$9;MATCH($B$5;MB41!$B$9:$B$17;0)-1;0))

Note that the $-signs indicate that the cell references may not be
changed by Excel as you copy the formual to another cell.

What I don't understand is that you always compare to B5 which results
in the same value in A9:A17.

ISNA(MATCH($B$5;MB41!$B$9:$B$17;0)) takes care of the fact that you
might enter something in B5 that is not in B9:B17 on MB41.

Hans

sorry, I think I got the point:

Add this in C9 on MB41:

=A9

Add this in C10:

=C9&"/"&A10

Copy the formula in C10 down to C17.

In A5 on Totals type this:

=IF(ISNA(MATCH($B$5;MB41!$B$9:$B$17;0));"";MB41!C1 7)

I added the help column C on MB41 because it would not be very handy to
include the concatenation in the formula. This is what it looks like:

1
1/2
1/2/3
1/2/3/4
1/2/3/4/5
1/2/3/4/5/6
1/2/3/4/5/6/7
1/2/3/4/5/6/7/8
1/2/3/4/5/6/7/8/9


Hope this is it.

Hans

This did not work either. It got even further away from the desired result,
saying false and Invalid.

However, I went back to what you sugessted before with this formula:

=IF(ISNA(MATCH($B$5,MB41!$B$9:$B$117,0)),"",
IF(MATCH($B$5,MB41!$B$9:$B$117,0)0,MB41!$A$9&"/"&$A$117,""))

I was not aware of the semicolon and comma, so I changed all of them to
comma's. This gave me the result that is on MB41 A5 with a slash, but did
not put the rest of them that matched, i.e. 103/ is all it gave me, when it
should have given me 103/111/116/117/127. Also this only shows up when I hit
the = sign and it shows me the formula result. It does not show it in the
cell of A5. A5 only shows the formula. Now what do I do?? Your help is
appreciated so much :-).

--
Thank you,
JR


"flummi" wrote:

sorry, I think I got the point:

Add this in C9 on MB41:

=A9

Add this in C10:

=C9&"/"&A10

Copy the formula in C10 down to C17.

In A5 on Totals type this:

=IF(ISNA(MATCH($B$5;MB41!$B$9:$B$17;0));"";MB41!C1 7)

I added the help column C on MB41 because it would not be very handy to
include the concatenation in the formula. This is what it looks like:

1
1/2
1/2/3
1/2/3/4
1/2/3/4/5
1/2/3/4/5/6
1/2/3/4/5/6/7
1/2/3/4/5/6/7/8
1/2/3/4/5/6/7/8/9


Hope this is it.

Hans

Hello JR,

sorry for the delay. Somehow I missed this.

If your problem still exists can you email me a sample workbook?

Hans

It does still exist, and I also have another challenge. How do I send you a
sample?
--
Thank you,
JR


"flummi" wrote:

Hello JR,

sorry for the delay. Somehow I missed this.

If your problem still exists can you email me a sample workbook?

Hans