 SOLVED

# LOGICAL FORMULA fail..!!!!

Hi guys,

In theory, a fairly simple task but I just can't figure out how to write the formula correctly (yes, a bit of an Excel doofus, apologies)! If you can help, I'll be hugely grateful.

Basically, just want to combine two IF's to produce a result, hence:

If the text in cell A2 says one thing and the text in cell B2 says another thing, then the resulting text in cell C2 will be the final thing.

Multiple possible permutations as you can see in the attached file but even so, you think I'd be able to get that right but noooooo...

Cheers.

Gyp

4 Replies
best response confirmed by Gypseyman (New Contributor)
Solution

# Re: LOGICAL FORMULA fail..!!!!

=VLOOKUP(K6&L6,CHOOSE({1,2},\$D\$6:\$D\$20&\$F\$6:\$F\$20,\$H\$6:\$H\$20),2,0)

I entered this formula in cell J6 as arrayformula with ctrl+shift+enter. Search values have to be entered in cells K6&L6 and result is returned in J6.

An alternative would be a nested formula like the one i entered in cell D32. Search values are in cell D30 and E30 in this example.

=IF(AND(D30=\$D\$6,E30=\$F\$6),\$H\$6,IF(AND(D30=\$D\$7,E30=\$F\$7),\$H\$7,IF(AND(D30=\$D\$8,E30=\$F\$8),\$H\$8,IF(AND(D30=\$D\$9,E30=\$F\$9),\$H\$9,IF(AND(D30=\$D\$10,E30=\$F\$10),\$H\$10,IF(AND(D30=\$D\$11,E30=\$F\$11),\$H\$11,IF(AND(D30=\$D\$12,E30=\$F\$12),\$H\$12,IF(AND(D30=\$D\$13,E30=\$F\$13),\$H\$13,IF(AND(D30=\$D\$14,E30=\$F\$14),\$H\$14,IF(AND(D30=\$D\$15,E30=\$F\$15),\$H\$15,IF(AND(D30=\$D\$16,E30=\$F\$16),\$H\$16,IF(AND(D30=\$D\$17,E30=\$F\$17),\$H\$17,IF(AND(D30=\$D\$18,E30=\$F\$18),\$H\$18,IF(AND(D30=\$D\$19,E30=\$F\$19),\$H\$19,IF(AND(D30=\$D\$20,E30=\$F\$20),\$H\$20,"")))))))))))))))

# Re: LOGICAL FORMULA fail..!!!!

Hi QP,

For some reason, when I open the file you sent and click on the cell J6 containing the formula, (even if only to look at it), as soon as I click away from it, the text within the cell changes from MAINTAIN to #N/A... I've tried removing the parentheses on the formula and then restoring them but that results in the actual formula showing as text in the cell. Rather strange...

On a positive note, however, the nested formula seems to work perfectly so I intend to go with that. Thanks so much!!! You have been a massive help and I really appreciate it, truly.

Cheers and thanks again.
Gyp.