IF or Vlookup or.... Which is best for this example:

[Guest]

I have 12 months and a value tied to each one. I was wondering what the best
way is to place the value in a cell if another cell has one of the months.

I assume I could put an IF statement in each cell I want the results in.
For Instance, using the values below: IF A1="October",10, A1="November",11,
A1="December",12, (etc,) would place the appropriate value in the cell(s)
that have that IF statement.

1 = January
2 = February
3 = March
4 = April
5 = May
6 = June
7 = July
8 = August
9 = September
10 = October
11 = November
12 = December

Should I use a long IF statement that lists each of the 12 months (assuming
an IF statement can do that), or try a Vlookup function (which I'm not that
familiar with?)

Thanks,

Steve

 

[Bob Phillips]

VLOOKUP is best

Assuming the dates and values are in Sheet2!A1:B12, then use

=VLOOKUP(A1,Sheet2!A1:B12,2,FALSE)

will return the value for the month in A1

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

If D1 houses a month name...

1.

=MATCH(D1,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0)

2.

=LOOKUP(D1,{"April",4;"August",8;"December",12;"February",2;"January",1;"July",7;"June",6;"March",3;"May",5;"November",11;"October",10;"September",9})

3.

=MONTH("1-"&D1)

 

[Guest]

I see how all of the examples given in the two posts work, however I failed
to properly explain the formula I need. My example had 1=Jan, 2=Feb, etc.
However, the values I gave are not the values I will be using. Here is a
better example of what I need.

Assume this data is cells A1:B12

0.123 January
0.068 February
0.133 March
0.088 April
0.096 May
0.101 June
0.096 July
0.083 August
0.102 September
0.251 October
0.12 November
0.082 December

I want be able to type into cell C1 the name of a Month, and have the
corresponding value be returned in Cell D1, D6 and D10.

So, It looks like I need a Vlookup formula in Cells D1, D6 and D10 that
references the value found in Cell C1 and displays the corresponding value
found in the A1:B12 range of Cells. Hopefully this is a better explaination.

Thanks,

Steve

 

[Myrna Larson]

You will waste less of people's time if you post your actual data at the
start.

Since the data to be returned is to the left of the lookup column

=INDEX(A1:A12,MATCH("March",B1:B12,0))

 

[Guest]

Sorry about that Myrna. The real data was lengthy, so I tried to edit it
down.

Regarding your answer, I was looking for a way to type in the name of a
month in one Cell (C1 in my example) and have the "answer" displayed in
another cell (D1 in my example). Is there anyway to substitute the word
"March" in your example with the Cell C1? That way the formula would 'look'
in Cell C1 and match it with the data in A1:B12. I tried various
combinations but none seem to work.

Thanks,

Steve

 

[Frank Kabel]

Hi
just replace the word with C1 and it will work. Try:
=INDEX(A1:A12,MATCH(C1,B1:B12,0))

 

[Guest]

Thanks! I tried =C1 and numerous variations.

By the way, In case you read this, I was wonding why use INDEX,MATCH and not
Vlookup?

Steve

 

[L. Howard Kittle]

This line from Myrna's reply should answer your "... why not VLOOKUP?"

"Since the data to be returned is to the left of the lookup column..."

VLOOKUP requires the returnable data to be to the right of the lookup
column.

HTH
Regards,
Howard

 

[Guest]

Interesting.

I read the line, but didn't put 2+2 together to realize that VLOOKUP
requires data to the right. Before I posted, I read the Excel Help file:

"VLOOKUP

Searches for a value in the leftmost column of a table, and then returns a
value in the same row from a column you specify in the table. Use VLOOKUP
instead of HLOOKUP when your comparison values are located in a column to the
left of the data you want to find."

I guess I had my comparison values transposed with the returns value.

Thanks for your help.

Steve

 

[chadt74]

"By the way, In case you read this, I was wonding why use INDEX,MATCH
and not
Vlookup?"

A VLOOKUP looks to the left most column to pick up what you are trying
to match. So in your example IF the data was :

Jan 0.124
Feb 1.150
Mar 0.456

You could use a VLOOKUP, but in your example you have the data to the
left of what you are trying to match and therefore you can not use a
VLOOKUP.

Chad