Excel formulas

[Una_McCrossan]

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

[#2]

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

@Una_McCrossan 

[#3]

@Una_McCrossan 

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.

 

[#4]

@Una_McCrossan 

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