Forum Discussion
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,"")))))))))))))))
=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,"")))))))))))))))
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.I'm glad my solution is helpful.
The VLOOKUP formula is an arrayformula that has to be entered with ctrl+shift+enter. If this formula is shown only as text, click in the formula bar and then press ctrl+shift+enter (in this order and press all of them) and this will put the { } around the formula and the formula will work (again). The { } can't be entered manually.
If you click in cell J6 then instead of clicking in another cell you can click on the red cross to the left of the formula bar. This will maintain the arrayformula. In attached file you can see the red cross on the upper left side. I added an blue arrow to indicate where to click.
