How to populate a column depending on the value of the another column in Excel?-CodePudding

I am struggling with this excel problem. I have an Excel with over 4000 rows. I am wondering how I can populate columns, for example, the left column with colors depending on the fruits from the right column. Is there a way that I could automatically edit the same values? Thank you

CodePudding user response：

You can create a lookup table of fruits and colors. For example, you might place the fruits in D2:D4 and colors in E2:E4. Then in your column A, use XMATCH:

=XLOOKUP(B1,$D$2:$D$4,$E$2:$E$4)

VLOOKUP will also work as will INDEX( MATCH. If you have XLOOKUP it is easy and safer than VLOOKUP.

VLOOKUP version:

=VLOOKUP(B1,$D$2:$E$4,2)

INDEX MATCH version:

=INDEX($E$2:$E$4, MATCH(B2,$D$2:$D$4,0) )

If you want to be able to edit the contents of fruits and colors, it would be easier to put them into an Excel Table (CTRL-t). Then your formula would be:

=INDEX(Table1[Color], MATCH(B2,Table1[Fruit],0) )

which would automatically expand and contract as you add/delete fruits.

You can also hard code the values of the table, but that is not a good idea because you would have to hard code it into each formula when you want to make changes. Here is hard coding:

=INDEX({"red","yellow","orange"}, MATCH(B3,{"apple","banana","orange"},0) )

CodePudding user response：

Try below formula. No need extra table.

=XLOOKUP(B1,{"apple","banana","orange"},{"red","yellow","orange"})