Excel formulas

Frequent Visitor

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

 

 ABC
1test4fourorange
2test5fivewhite
3test8eightgreen
4test7sevenorange
5test9ninesilver
6cartenblack
7test6sixgreen
8test2twogreen
9test3threeorange
10test1onegreen

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 

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

S1819.png

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.

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

nested if.JPG