SOLVED

analysis of an entry

New Contributor

I have a series of labels e.g. g5,c5,p5, each have a distinct value recorded in each cell

i.e g5 = 5, c5 = 15, p5 = 7, 

so what I want to do in another cell is enter any one of the 3 labels and get the corresponding value?

I assume it's if and or statement but can't get it to work?

0 Likes
 
 
 

 

 
 
 
5 Replies

@MARKVEASEY 

Let's say you enter a "label" such as c5 in A1.

In the cell next to it, enter the formula =INDIRECT(A1)

Does that do what you want?

@Hans Vogelaar 

No not really?

 

i have a data base of values, that are identified against a label, each label has a different value,

so I want to enter any of the labels in a cell and next to it get the corresponding value for that label as identified in the data table.

Its basically for me to get a value by recording the differing labels only?

to be truthful, I want to do an MTM analysis using descriptions of action, each has a value and then at the end of the list I want a total.

i have managed to get my sheet to enter a value specific to a description, but I want to enter any label in a cell and the corresponding value come back in the cell next to it.

oh yes, there are 40 distinct values, each one has a distinct code! so I want to enter anyone of 40 codes and get a value back
best response confirmed by Hans Vogelaar (MVP)
Solution

@MARKVEASEY 

What I use is the "IFS" function.

create a table with your labels and values, 

then use =IFS to identify what label you typed and find the corresponding value.

 

Example, I type a label in H3, in I3 I have this IFS formula to go find that label on my table and give me the corresponding value:

=IFS(H3=$AF$1,$AE$1,H3=$AD$1,$AC$1,H3=$AB$1,$AA$1,H3=$Z$1,$Y$1,H3=$X$1,$W$1,H3=$V$1,$U$1,H3=$AH$1,$AG$1)

absolutely perfect
thx