Oct 03 2022 09:47 PM
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
Oct 04 2022 01:29 AM - edited Oct 04 2022 01:30 AM
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)
Oct 04 2022 03:34 AM
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.
Oct 04 2022 04:31 AM
=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.