Jan 06 2022 02:09 PM
I am not an excel wizard... need help!
I am trying to write two formulas that I think are look ups or a type of match formula. Likely it is a compound formula. Any help appreciated!
First: I have this chart where we will be tracking two things - Energy and Pleasantness on a 1-10 scale.
Date | Energy | Pleasantness | Feeling |
1-Jan | 10 | 5 | Shocked |
2-Jan | 3 | 6 | |
3-Jan | 4 | 7 | |
4-Jan | 1 | 5 | |
5-Jan | 4 | 6 | |
6-Jan | 5 | 5 | |
7-Jan | 8 | 5 | |
8-Jan | 2 | 8 | |
9-Jan | 4 | 8 | |
10-Jan | 3 | 9 | |
11-Jan | 4 | 8 | |
12-Jan | 4 | 7 | |
13-Jan | 3 | 9 | |
14-Jan | 2 | 8 | |
15-Jan | 6 | 9 | |
16-Jan | 1 | 7 | |
17-Jan | 5 | 6 | |
18-Jan | 1 | 5 | |
19-Jan | 2 | 6 | |
20-Jan | 3 | 7 | |
21-Jan | 5 | 8 | |
22-Jan | 3 | 8 | |
23-Jan | 5 | 8 | |
24-Jan | 2 | 8 | |
25-Jan | 3 | 8 | |
26-Jan | 4 | 8 | |
27-Jan | 1 | 8 | |
28-Jan | 2 | 9 | |
29-Jan | 3 | 6 | |
30-Jan | 4 | 7 |
I want to put a formula in the feeling that will take those two numbers and look them up on another sheet and "return" the Feeling word.
Here is the chart - that I want it to find the word in-
Energy | |||||||||||
10 | Enraged | Panicked | Stressed | Jittery | Shocked | Surprised | Upbeat | Festive | Exhilarated | Ecstatic | |
9 | Livid | Furious | Frustrated | Tense | Hyper | Cheerful | Motivated | Inspired | Elated | ||
8 | Fuming | Frightened | Angry | Nervous | Restless | Lively | Enthusiastic | Optimistic | Excited | ||
7 | Anxious | Apprehensive | Worried | Annoyed | Pleased | Happy | Focused | Proud | Thrilled | ||
6 | Repulsed | Troubled | Concerned | Peeved | Pleasant | Joyful | Hopeful | Playful | Blissful | ||
5 | Disgusted | Slum | Disappointed | Down | Apathetic | At Ease | Easy Going | Content | Loving | Fulfilled | |
4 | Pessimistic | Morose | Discouraged | Mad | Bored | Calm | Secure | Satisfied | Grateful | Touched | |
3 | Alienated | Miserable | Lonely | Disheartened | Tired | Relaxed | Chill | Restful | Blessed | Balanced | |
2 | Despondent | Depressed | Sullen | Exhausted | Fatigued | Mellow | Thoughtful | Peaceful | Comfy | Carefree | |
1 | Despair | Hopeless | Desolate | Spent | Drained | Sleepy | Complacent | Tranquil | Cozy | Serene | |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Pleasantness |
I would like to do the opposite too as I am working on a tracking mechanism for my kids to help them grow in their emotional awareness.
Look up the word "Despair" and return - Energy =1; Pleasantness = 1
Jan 09 2022 06:59 PM
Hello!
You can combine the index and match formulas:
=INDEX($J$4:$S$13,MATCH(B4,$I$4:$I$13,0),MATCH(C4,$J$3:$S$3,0))
Attached is an excel file with the formula.
Jan 10 2022 02:27 PM
@alannavarro Thank you so much! I thought it would be something like that, but really would not have gotten there without help!
Can I do the same thing if I have the word and want to pull out the numbers?
Take shocked for example... - how do I return 10 in the energy column and 5 in the pleasantness column?
Jan 12 2022 10:39 AM
=INDEX($A$1:$A$10,MATCH(1,MMULT(--($B$1:$K$10=E16),{1;1;1;1;1;1;1;1;1;1}),0))
=INDEX($B$11:$K$11,MATCH(1,MMULT({1,1,1,1,1,1,1,1,1,1},--($B$1:$K$10=E16)),0))
You can try above formulas to return the energy column and pleasantness column as shown in the attached file.