Looking up Text in a chart by 2 variables and returning it

Copper Contributor

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.

DateEnergy PleasantnessFeeling
1-Jan105Shocked
2-Jan36 
3-Jan47 
4-Jan15 
5-Jan46 
6-Jan55 
7-Jan85 
8-Jan28 
9-Jan48 
10-Jan39 
11-Jan48 
12-Jan47 
13-Jan39 
14-Jan28 
15-Jan69 
16-Jan17 
17-Jan56 
18-Jan15 
19-Jan26 
20-Jan37 
21-Jan58 
22-Jan38 
23-Jan58 
24-Jan28 
25-Jan38 
26-Jan48 
27-Jan18 
28-Jan29 
29-Jan36 
30-Jan47 

 

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           
10EnragedPanickedStressed JitteryShockedSurprisedUpbeatFestiveExhilaratedEcstatic 
9LividFuriousFrustratedTense HyperCheerfulMotivatedInspiredElated 
8FumingFrightenedAngryNervousRestless LivelyEnthusiasticOptimisticExcited 
7AnxiousApprehensiveWorried AnnoyedPleasedHappyFocusedProudThrilled 
6RepulsedTroubledConcerned  PeevedPleasantJoyfulHopefulPlayfulBlissful 
5DisgustedSlumDisappointedDownApatheticAt EaseEasy GoingContentLovingFulfilled 
4PessimisticMoroseDiscouragedMadBoredCalmSecureSatisfiedGratefulTouched 
3AlienatedMiserableLonelyDisheartenedTiredRelaxedChillRestfulBlessedBalanced 
2DespondentDepressedSullenExhaustedFatiguedMellowThoughtfulPeacefulComfyCarefree 
1DespairHopelessDesolateSpentDrainedSleepyComplacentTranquilCozySerene 
 12345678910Pleasantness

 

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

3 Replies

@Charlene75 

 

 

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.

@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?

@Charlene75 

=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.