How to populate fields with related values from another table in Excel?

Question

I have a column of values that says White, Black, Red, Blue, etc. I would like that row to convert to the colours' ID instead of the colour name. For example, White will be 1, Black will be 2 and so on.

I have almost 20 such values of colours corresponding with their IDs and I've heard that nested IF statements in Excel doesn't go beyond 7 IFs. How can I populate that column with the values' IDs instead of the values themselves?

There is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) and INDEX(array,MATCH(lookup_value,lookup_array,[match_type]). Now you need to show the example of your data to get the exact formula. — zipa
– zipa, Commented Sep 15, 2016 at 12:37

Kᴀτᴢ · Accepted Answer · 2016-09-15 12:35:42Z

2

Use the switch function (supports 126 values):

Example:

=SWITCH(A2,1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday","No match")

Complete documentation: https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

answered Sep 15, 2016 at 12:35

Kᴀτᴢ

2,1966 gold badges32 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to populate fields with related values from another table in Excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related