Compare 2 columns with words to 1 and to get the 3rd word.

  • Thread starter Thread starter Postman
  • Start date Start date
P

Postman

Hi All

I have question for you. I have letter "h" (from high) in column A1 and
letter "L" (from low) in column B1. In column C1 i would like to get the
avarage from them which is "m" (from Medium). How to do it?

Thanks in advance
 
Put this formula in C2:

=AVERAGE(A2,B2)

and copy down as required.

Hope this helps.

Pete
 
Try

=CHOOSE(ROUND(AVERAGE(VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0),
VLOOKUP(B1,{"high",3;"low",1;"medium",2},2,0)),0),"low","medium","high")
 
it's not working. Any other idea?

Jacob Skaria said:
Try

=CHOOSE(ROUND(AVERAGE(VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0),
VLOOKUP(B1,{"high",3;"low",1;"medium",2},2,0)),0),"low","medium","high")
Click to expand...
 
In cell A1 and B1 put one of the 3 status (high, low, medium) and then try
the formula in cell C1. When you say 'not working' what is the formula result.
 
In A1 i wrote - High
In B1 i wrote - Low
In C1 i paste the formula and give me error in "(VLOOKUP(A1,{" in "A1,"
 
and what happens when you try the below formula with cell A1 with "low",
"medium etc;

=VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0)
 
nothing happens - gives me a error. :( i paste the whole formula -
=CHOOSE(ROUND(AVERAGE(VLOOKUP(A1,{"high",3;"low",1;"medium",2},2,0),VLOOKUP(B1,{"high",3;"low",1;"medium",2},2,0)),0),"low","medium","high")
 
In Sheet2 enter the below info in ColA/B

ColA ColB
high 3
medium 2
low 1

and use the below formula in a different sheet

=INDEX(Sheet2!A1:A3,MATCH(ROUND(AVERAGE(
VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0),
VLOOKUP(B1,Sheet2!$A$1:$B$3,2,0)),0),Sheet2!B1:B3,0))
 
Well, if you change all the "high", "low", "medium" in Jacob's formula to: H,
L, M
for consistency with your actual lookup values in A1 & B1 (you mentioned:
"h", "L", "m"), think you'd get it to work. Data matched must be consistent
for it to work.
 
I did what you said in sheet2 and in sheet 3 i just paste the formula in cell
A1 and it gives me a error in "A3,MATCH"
 
Let me get clear.
Sheet 1.
cell a1 - High
Cell b1 - Low
Cell C1 - looking to get formula to recieve avarage from high and low, which
is "medium". So in Cell C1 i need to have Medium.

The second this is: Cell A1 - High, Cell B1 - Medium, in C1 i need High or
Medium - it doesn't matter for me, because i can change it in formulas i
think. It depends what i'm gonna need to receive.

Nick
 
In Sheet3

cell A1 enter high
cell B1 enter low
cell C1 apply the formula..

Do you have the argument separator as comma or semicolon....Try changing
that to semicolon...
 
Back
Top