IF problem ?? cell contains text and numbers

**Dr Win** · 06-03-2012, 07:27 PM

How you wood fix this:

If B2 contains AA & A1 (as text from drop down list) and C2 contains 1A (as text from list) then return formula: (2+2)*3 results into cell F2

**vlady** · 06-03-2012, 07:45 PM

Hello

=IF(AND(B2="AA & A1",C2="1A"),(2+2)*3,"")

but i think there is more on this rules you should provide.
where did (2+2)*3 came from.
How about if
AA& A1 - 1.5A etch.

**FDibbins** · 06-03-2012, 07:47 PM

This is probably way too simplistic, but without knowing all the details, try....

=IF(AND(B2="AA & A1",C2="1A"),(2+2)*3,"")

When i entered that, i got a time format, o you may waht to change the format to general or number?

**Dr Win** · 06-03-2012, 09:47 PM

Thank you for answer, it is working on sample sheet, but I am trying to make-work in my project and I am stack

Can you help me to input this formula into cell N12, ;

=IF(AND(I12="3up m&f",J12="6"),((INDEX(Sheet2!D:D,MATCH(L13,Sheet2!E:E),1))-( INDEX(Sheet2!$D:$D,MATCH(K12,Sheet2!D:D)+1,1)))*2+(INDEX(Sheet2!G:G,MATCH(L13,Sheet2!E:E),1))),"")/60/60/24

correct (or) has to show 1:09.70,.....

**vlady** · 06-03-2012, 09:57 PM

paste this.

=IF(AND(J12=6,I12="3up m&f"),((INDEX(Sheet2!D:D,MATCH(L13,Sheet2!E:E),1))-( INDEX(Sheet2!$D:$D,MATCH(K12,Sheet2!D:D)+1,1)))*2+(INDEX(Sheet2!G:G,MATCH(L13,Sheet2!E:E),1)))/60/60/24

you'll receive 0:00.00 if j12 is not 6 and not 3up m&f

**Dr Win** · 06-03-2012, 11:10 PM

Thank you, and it is working.
So how you will make all combination, from that lists, (J12 has: 8 numbers but I like to treat them as text, because may change list and add some letters) and I12 has list of 5 items / text. There is 40 combinations and I like to put different formulas for all 40. Just need sample for start. So first one is this like is now, so how I will add second one which will be when J12 is 6 but I12 is 3up fem and etc,..

And correct results in N12 is 1:09.48, if you can check that.

**vlady** · 06-04-2012, 01:21 AM

Just suggestion, create a table for all combination then use index match to display the result in K12..
then you do not need the =IF(AND(J12=6,I12="3up m&f") anymore the formula in N12 will rely in I,J,K,L and M

FIFTS (1).xls

I hope I got the idea correct.