 # 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

# Re: Excel formulas

 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)​``

# Re: Excel formulas

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.

# Re: Excel formulas

=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. 