How to automatically feed a cell value from a range of values, based on its matching condition with other cell value

Question

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.

Sample sheet

Answer 1

This is the formula, that worked for me (for anybody's reference): I created another reference sheet, stating the types of work & their significance. From that sheet, I'm using either vlookup, filter, xlookup.Using gforms for inputting my data.

=ARRAYFORMULA(IFS(ROW(D:D)=1,"Significance",A:A="","",TRUE,VLOOKUP(D:D,Reference!$A:$B,2,0)))

Answer 2

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

Answer 3

XLOOKUP() may work. Try-

=XLOOKUP(D2,A2:A,B2:B)

Or FILTER() function like-

=FILTER(B2:B,A2:A=D2)