software

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!B9amp;quot;:quot;amp;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: www.excelforum.com/member.php...oamp;userid=30736
View this thread: www.excelforum.com/showthread...hreadid=508920This is what your formual should lóok like for the following example

columns A to F:

afab:cdabcd
b
c
d
e
f
g
h

Formula in C1;
=IF(ISNA(MATCH(B1;A1:A8;0));quot;quot;;IF(MATCH(B1;A1:A8;0 )gt;0;E1amp;quot;:quot;amp;F1;quot;quot;))

Adapt the formula to your needs.

HansIt is saying invalid for the answer.

quot;MB41quot; is the Sheet that I am comparing to cell B5 on Sheet quot;Totalsquot;. 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));quot;quot;;
IF(MATCH(B5;MB41!b9:B117;0)gt;0;MB41!A1amp;quot;:quot;amp;A117;quot;quot;) )

Where did I go wrong?
--
Thank you,
JRquot;flummiquot; wrote:

gt; This is what your formual should lóok like for the following example
gt;
gt; columns A to F:
gt;
gt; afab:cdabcd
gt; b
gt; c
gt; d
gt; e
gt; f
gt; g
gt; h
gt;
gt; Formula in C1;
gt; =IF(ISNA(MATCH(B1;A1:A8;0));quot;quot;;IF(MATCH(B1;A1:A8;0 )gt;0;E1amp;quot;:quot;amp;F1;quot;quot;))
gt;
gt; Adapt the formula to your needs.
gt;
gt; Hans
gt;
gt;

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,
JRquot;EdMacquot; wrote:

gt;
gt; You are approaching the problem the back to front.
gt;
gt; What you need is =If(condition1,do this,if not do this).
gt;
gt; If you want to meet more than one condition then
gt;
gt; =If(and(condition1,condition2,......),do this,if not do this)
gt;
gt; Hope this helps
gt;
gt; Ed
gt;
gt;
gt; --
gt; EdMac
gt; ------------------------------------------------------------------------
gt; EdMac's Profile: www.excelforum.com/member.php...oamp;userid=30736
gt; View this thread: www.excelforum.com/showthread...hreadid=508920
gt;
gt;

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

Here's the range on MB41:

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

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));quot;quot;;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.

Hanssorry, I think I got the point:

Add this in C9 on MB41:

=A9

Add this in C10:

=C9amp;quot;/quot;amp;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));quot;quot;;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/9Hope this is it.

HansThis 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)),quot;quot;,
IF(MATCH($B$5,MB41!$B$9:$B$117,0)gt;0,MB41!$A$9amp;quot;/quot;amp;$A$117,quot;quot;))

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,
JRquot;flummiquot; wrote:

gt; sorry, I think I got the point:
gt;
gt; Add this in C9 on MB41:
gt;
gt; =A9
gt;
gt; Add this in C10:
gt;
gt; =C9amp;quot;/quot;amp;A10
gt;
gt; Copy the formula in C10 down to C17.
gt;
gt; In A5 on Totals type this:
gt;
gt; =IF(ISNA(MATCH($B$5;MB41!$B$9:$B$17;0));quot;quot;;MB41!C1 7)
gt;
gt; I added the help column C on MB41 because it would not be very handy to
gt; include the concatenation in the formula. This is what it looks like:
gt;
gt; 1
gt; 1/2
gt; 1/2/3
gt; 1/2/3/4
gt; 1/2/3/4/5
gt; 1/2/3/4/5/6
gt; 1/2/3/4/5/6/7
gt; 1/2/3/4/5/6/7/8
gt; 1/2/3/4/5/6/7/8/9
gt;
gt;
gt; Hope this is it.
gt;
gt; Hans
gt;
gt;

Hello JR,

sorry for the delay. Somehow I missed this.

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

HansIt does still exist, and I also have another challenge. How do I send you a
sample?
--
Thank you,
JRquot;flummiquot; wrote:

gt; Hello JR,
gt;
gt; sorry for the delay. Somehow I missed this.
gt;
gt; If your problem still exists can you email me a sample workbook?
gt;
gt; Hans
gt;
gt;