Hi Rajula,

If you are still having problems, have you considered using the Index
function to select matches of A1 & B1.
As per the attached, you can allocate values to various combinations,
shown as green for A1 and purple for B1 to produce the result you
require shown as yellow.

The statement is:


=IF(ISERROR(OR(MATCH(A1,F$10:F$14,0),MATCH(B1,G$9: K$9,0))),"",INDEX(G$10:K$14,MATCH(A1,F$10:F$14,0), MATCH(B1,G$9:K$9,0)))

and the table can be increased to match your current and future needs,
as is shown in the coloured zone.

Just another possibility.

Attached:
http://www.excelforum.com/attachment...3&d=1145621998

Hope this helps

Rajula Wrote:
Hi,

It giving all kinds of funny problems.
Out of the 3 defined formula, Only onetoseven works. eighttonine
formula
doesnt work using master.

Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined
names
the formula is for A1, B1...

Regards


"Dave Peterson" wrote:

Since each one of these conditions is mutually exclusive, you could
just
concatenate those strings:

=IF(AND(A1="H",B1="H"),"H","")
&IF(AND(A1="H",B1="M"),"H","")
&IF(AND(A1="H",B1="L"),"M","")
&IF(AND(A1="M",B1="H"),"M","")
&IF(AND(A1="M",B1="M"),"M","")
&IF(AND(A1="M",B1="L"),"L","")
&IF(AND(A1="L",B1="H"),"M","")
(and so forth)


Rajula wrote:

I used the following link and solved the problem of using more than
7 nested
if statements http://www.cpearson.com/excel/nested.htm.

I now have 3 defined names (3 formulas). Master, onetoseven and
eighttonine.
Master is onetoseven and eighttonine combined.

I am using Master in cell C1. Now i need to use Master in cell C2,
C3,
....etc for hundreds of rows.

My problem is 'onetoseven' has
IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H",
IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M",
IF(AND(A1="M",B1="M"),"M",
IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", )))))))

and 'eighttonine' has
IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",))

Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have
to get
for A3,B3... and so forth. dynamically..

Is there a solution to this.

Regards
Rajula

--

Dave Peterson



+-------------------------------------------------------------------+
|Filename: Mif.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4673 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=534670