Forum Discussion
Excel formulas
Hi there,
I would like to populate a cell, based on the values that are chosen from the previous two cells, which are drop down lists.
For example
Column 1 - ''Ten'' is chosen, Column 2, ''Black'' is chosen, Column 3, Result is ''Car''
(Based on prefilled data in another column)
Any help is appreciated, thank you.
Una
3 Replies
=IF(AND(A1=$F$1,B1=$G$1),$H$1,IF(AND(A1=$F$2,B1=$G$2),$H$2,IF(AND(A1=$F$3,B1=$G$3),$H$3,IF(AND(A1=$F$4,B1=$G$4),$H$4,IF(AND(A1=$F$5,B1=$G$5),$H$5,IF(AND(A1=$F$6,B1=$G$6),$H$6,IF(AND(A1=$F$7,B1=$G$7),$H$7,IF(AND(A1=$F$8,B1=$G$8),$H$8,IF(AND(A1=$F$9,B1=$G$9),$H$9,IF(AND(A1=$F$10,B1=$G$10),$H$10,IF(AND(A1=$F$11,B1=$G$11),$H$11,"")))))))))))
An alternative could be a nested IF formula. If you work with Office365 or 2021 you can apply IFS.
Alternatively, create a two-way lookup table. In the screenshot below, it is on the same sheet, but it can be on another sheet just as well.
The formula in C2 is
=INDEX($F$2:$I$11,MATCH(A2,$E$2:$E$11,0),MATCH(B2,$F$1:$I$1,0))
This can be filled down if required.
A B C 1 test4 four orange 2 test5 five white 3 test8 eight green 4 test7 seven orange 5 test9 nine silver 6 car ten black 7 test6 six green 8 test2 two green 9 test3 three orange 10 test1 one green If the data preset is like above, than your target function will be :
=XLOOKUP($I$3&$J$3,$C$2:$C$11&$D$2:$D$11,$B$2:$B$11,"NA",0)
