S
Scottie318
How Do I Convert Text Month to a Number using VLookup. Please help!
Thanks
Scott
Thanks
Scott
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
E
Earl Kiosterud
Scottie,
Do you really need to use Vlookup? You can get the number of the month in a
cell containing a date with formatting. Format - Cells - Custom:
m
mm
If you need to extract the month number from a date for use in a formula,
use this expression:
=Month(A2)
Do you really need to use Vlookup? You can get the number of the month in a
cell containing a date with formatting. Format - Cells - Custom:
m
mm
If you need to extract the month number from a date for use in a formula,
use this expression:
=Month(A2)
S
Scottie318
My professor is requesting that I use Vlookup. I'm somewhat new to
Excel and I have never learned how to use Vlookup to convert text month
to a number. Months must be in ascending sequence in the table. Maybe
that will help clarify things? I appreciate your help.
Thanks
Scott
Excel and I have never learned how to use Vlookup to convert text month
to a number. Months must be in ascending sequence in the table. Maybe
that will help clarify things? I appreciate your help.
Thanks
Scott
L
Laura Cook
If your professor is requesting you use VLOOKUP, then you should not have to
put the months in the table in ascending order (as opposed to when you use
LOOKUP). If you set the forth argument in the VLOOKUP formula to "FALSE",
then it will look for an exact match. Do a search for VLOOKUP in Excel's
help files.
put the months in the table in ascending order (as opposed to when you use
LOOKUP). If you set the forth argument in the VLOOKUP formula to "FALSE",
then it will look for an exact match. Do a search for VLOOKUP in Excel's
help files.
D
DavidP
If you want to use vlookup then the table has to be sorted
alphabetically
Apr 4
Aug 8
Dec 12
Feb 2
Jan 1
Jul 7
Jun 6
Mar 3
May 5
Nov 11
Oct 10
Sep 9
Assuming the item you want to search for is in cell(E1) and the table
above is in cells(A1:B12) then use this formula:
=VLOOKUP(E1,A1:B12,2)
David
alphabetically
Apr 4
Aug 8
Dec 12
Feb 2
Jan 1
Jul 7
Jun 6
Mar 3
May 5
Nov 11
Oct 10
Sep 9
Assuming the item you want to search for is in cell(E1) and the table
above is in cells(A1:B12) then use this formula:
=VLOOKUP(E1,A1:B12,2)
David
K
Ken Wright
See Laura's note - there is no need for sorting if you use VLOOKUPs optional 4th argument.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
DavidP said:If you want to use vlookup then the table has to be sorted
alphabetically
Apr 4
Aug 8
Dec 12
Feb 2
Jan 1
Jul 7
Jun 6
Mar 3
May 5
Nov 11
Oct 10
Sep 9
Assuming the item you want to search for is in cell(E1) and the table
above is in cells(A1:B12) then use this formula:
=VLOOKUP(E1,A1:B12,2)
David
K
Ken Wright
Excel's help on VLOOKUP is quite good, but maybe this example (Below the dashed line) that I
posted to a previous thread will help your understanding. Just imagine the data in the left
column is months, and that the data in the second column is the corresponding Month number. (You
wouldn't need any of the other columns for this example).
If you really want to get fancy, then you should make sure you understand the data below well, and
then take a look at arrays, as this can all be done in a single cell, without the need for a
table, eg:-
Assuming the month is in A1
=VLOOKUP(A1,{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov
",11;"Dec",12},2,0)
This will give you the correct month number for whatever text is in A1, all without the need for a
table of data in the worksheet.
This however, will buy you NO MARKS AT ALL from your examiner, without a thorough understanding of
how it works, and you WILL be asked to explain it. If it means enough to you, it will be well
worth the time and effort to try and understand it. If you want an explanation, then simply post
back, but I can't stress enough, that understanding the example below first is an absolute
prerequisite, or it will make no sense whatsoever.
---------------------------------------------------
VLOOKUP
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
Assume you have a table of values, 5 Columns Wide by say 100 rows long. Let's assume that range
is A5:E105 and is as follows:-
A B C D E
1 ID Name Add1 Add2 Add3
2 10004
3
4 ID Name Add1 Add2 Add3
5 10021 John 25 Blue Walk
6 10008 Bill 23 Green Way
7 10004 Fred 47 Red Drive
8 10144 Sue 19 Yellow Road
9 10254 Sarah 45 Black Crescent
.... etc
Now, lets assume that you have a cell A2 that you want to be able to put in an ID number that
resides somewhere in your list in Col A, but that when you do that you would like all the relevant
information from the table to do with that ID to be returned to cells B2:E2.
You would use a VLOOKUP formula in each cell such as the following:-
In cell B2 =VLOOKUP(A2,A5:E105,2,0)
What this does is to look first at the value in cell A2, and then it goes and looks for that value
in Col A (The first column in the range A5:E105). When it finds it, it will then look at the data
in the 2nd column (The 2 in the formula), and as long as the data in Col A is an exact match with
A2 (The 0 specifies that it has to be - Occasions exist when you want only the nearest match),
then it will bring back whatever it finds in Col 2 of that matching row.
This means that to get back all the data, you would use the following formulas in B2:E2
B2 =VLOOKUP(A2,A5:E105,2,0)
C2 =VLOOKUP(A2,A5:E105,3,0)
D2 =VLOOKUP(A2,A5:E105,4,0)
E2 =VLOOKUP(A2,A5:E105,5,0)
Note the column identifier goes up by 1 in each case. This mean sthat you will get back the data
from Cols B / C / D / E respectively.
This will give you the following in row 2:-
A B C D E
1 ID Name Add1 Add2 Add3
2 10004 Fred 47 Red Drive
The formula I quoted right at the start:-
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
works as follows:-
The lookup_value is your data in A2
The table_array is your table of data A5:E105
The Col_Index_Num is the column in the table you want it to pull data from when it finds a match
in the first column.
The range_lookup is the bit that says it either has to be an exact match (0) or just a close match
(1 or omitted)
posted to a previous thread will help your understanding. Just imagine the data in the left
column is months, and that the data in the second column is the corresponding Month number. (You
wouldn't need any of the other columns for this example).
If you really want to get fancy, then you should make sure you understand the data below well, and
then take a look at arrays, as this can all be done in a single cell, without the need for a
table, eg:-
Assuming the month is in A1
=VLOOKUP(A1,{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov
",11;"Dec",12},2,0)
This will give you the correct month number for whatever text is in A1, all without the need for a
table of data in the worksheet.
This however, will buy you NO MARKS AT ALL from your examiner, without a thorough understanding of
how it works, and you WILL be asked to explain it. If it means enough to you, it will be well
worth the time and effort to try and understand it. If you want an explanation, then simply post
back, but I can't stress enough, that understanding the example below first is an absolute
prerequisite, or it will make no sense whatsoever.
---------------------------------------------------
VLOOKUP
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
Assume you have a table of values, 5 Columns Wide by say 100 rows long. Let's assume that range
is A5:E105 and is as follows:-
A B C D E
1 ID Name Add1 Add2 Add3
2 10004
3
4 ID Name Add1 Add2 Add3
5 10021 John 25 Blue Walk
6 10008 Bill 23 Green Way
7 10004 Fred 47 Red Drive
8 10144 Sue 19 Yellow Road
9 10254 Sarah 45 Black Crescent
.... etc
Now, lets assume that you have a cell A2 that you want to be able to put in an ID number that
resides somewhere in your list in Col A, but that when you do that you would like all the relevant
information from the table to do with that ID to be returned to cells B2:E2.
You would use a VLOOKUP formula in each cell such as the following:-
In cell B2 =VLOOKUP(A2,A5:E105,2,0)
What this does is to look first at the value in cell A2, and then it goes and looks for that value
in Col A (The first column in the range A5:E105). When it finds it, it will then look at the data
in the 2nd column (The 2 in the formula), and as long as the data in Col A is an exact match with
A2 (The 0 specifies that it has to be - Occasions exist when you want only the nearest match),
then it will bring back whatever it finds in Col 2 of that matching row.
This means that to get back all the data, you would use the following formulas in B2:E2
B2 =VLOOKUP(A2,A5:E105,2,0)
C2 =VLOOKUP(A2,A5:E105,3,0)
D2 =VLOOKUP(A2,A5:E105,4,0)
E2 =VLOOKUP(A2,A5:E105,5,0)
Note the column identifier goes up by 1 in each case. This mean sthat you will get back the data
from Cols B / C / D / E respectively.
This will give you the following in row 2:-
A B C D E
1 ID Name Add1 Add2 Add3
2 10004 Fred 47 Red Drive
The formula I quoted right at the start:-
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
works as follows:-
The lookup_value is your data in A2
The table_array is your table of data A5:E105
The Col_Index_Num is the column in the table you want it to pull data from when it finds a match
in the first column.
The range_lookup is the bit that says it either has to be an exact match (0) or just a close match
(1 or omitted)
B
Bob Langley
Scottie318 said:How Do I Convert Text Month to a Number using VLookup. Please help!
Thanks
Scott
If your professor wants vlookup used, there are several ways already
mentioned. If you actually want to convert the text name of a month
to a number, this works: Assume the word January in E21.
=MONTH(DATEVALUE(1&TEXT(E21,"MMM")&1))will get you the number one (1).