I'm making a time-spending tracker based on the work I do every hour of the day. Now, suppose I have 28 types of work listed in my tracker (which I also have to increase from time to time), and I have about 8 significance values that I have decided to relate to these 28 types of work, predefined.

I want that, as soon as I enter a type of work in cell 1 - I want the adjacent cell 2 to get automatically populated with a significance value (from a range of 8 values) that is pre-definitely set by me. Every time I input a new or old occurrence of a type of work, the adjacent cell should automatically get matched with its relevant significance value & automatically get populated in real-time.

I know how to do it using IF, IFS, and IF_OR conditions, but I feel that based on the ever-expanding types of work & significance values, the above formulas will be very big, complicated, and repetitive in the future. I feel there's a more efficient way to achieve it. Also, I don't want it to be selected from a drop-down list.

Guys, please help me out with the most efficient way to handle this. TUIA :) Also, I've added a snapshot and a sample sheet describing the problem.

CodePudding user response：

You can use this formula for a whole column:

=INDEX(IFERROR(VLOOKUP(C14:C,A2:B9,2,0)))

Adapt the ranges to your actual tables in order to include in the second argument all the potential values and their significances