Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using a number as text to "lookup" a match formatted in a
I think this depreciation project is going to be the death of me. I hope
someone can help. In one column of the template the years are entered as text format, but column I need to use for the vlookup is formatted in a custom year. How can I get the formula to recognize the text format year is the same as the custom year format. I have spent an obscene amount of hours trying to figure this out. Any help would be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using a number as text to "lookup" a match formatted in a
It depends on what you mean by custom year format.
=vlookup() and =match() don't care about what you see (how the cell is formatted), they care about the values that are in the cells. So if the table contained years (not real dates formatted as yyyy to just show the year), you could use a formula like: =vlookup(--a1,sheet2!a:b,2,false) --a1 will coerce the text in A1 to a real number. And then that real number can match one of the values in column A of sheet2. -A1 will make the text number a real number, but negative (well, if A1 looked like a positive number). --A1 will convert the negative number to a positive number. =============== If you really have dates in column A of sheet2, but you formatted the cells to just show the date, you could use a formula like: =index(sheet2!b1:b999,match(--a1,year(sheet2!a1:a999),0)) (this will match the coerced number in A1 to the year (a real number).) or =index(sheet2!b1:b999,match(a1,text(sheet2!a1:a999 ,"yyyy")),0)) (This will match the text in A1 with the text year. (=text() returns text, not numbers.)) These are an array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Maxine wrote: I think this depreciation project is going to be the death of me. I hope someone can help. In one column of the template the years are entered as text format, but column I need to use for the vlookup is formatted in a custom year. How can I get the formula to recognize the text format year is the same as the custom year format. I have spent an obscene amount of hours trying to figure this out. Any help would be appreciated. Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using a number as text to "lookup" a match formatted i
When I enter the vlookup formula you suggested, I get a value error.
"Dave Peterson" wrote: It depends on what you mean by custom year format. =vlookup() and =match() don't care about what you see (how the cell is formatted), they care about the values that are in the cells. So if the table contained years (not real dates formatted as yyyy to just show the year), you could use a formula like: =vlookup(--a1,sheet2!a:b,2,false) --a1 will coerce the text in A1 to a real number. And then that real number can match one of the values in column A of sheet2. -A1 will make the text number a real number, but negative (well, if A1 looked like a positive number). --A1 will convert the negative number to a positive number. =============== If you really have dates in column A of sheet2, but you formatted the cells to just show the date, you could use a formula like: =index(sheet2!b1:b999,match(--a1,year(sheet2!a1:a999),0)) (this will match the coerced number in A1 to the year (a real number).) or =index(sheet2!b1:b999,match(a1,text(sheet2!a1:a999 ,"yyyy")),0)) (This will match the text in A1 with the text year. (=text() returns text, not numbers.)) These are an array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Maxine wrote: I think this depreciation project is going to be the death of me. I hope someone can help. In one column of the template the years are entered as text format, but column I need to use for the vlookup is formatted in a custom year. How can I get the formula to recognize the text format year is the same as the custom year format. I have spent an obscene amount of hours trying to figure this out. Any help would be appreciated. Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using a number as text to "lookup" a match formatted i
Which formula?
And which part of which formula? If it's this portion: =--A1 Then that means that the value in A1 isn't close enough to being a number and excel couldn't coerce it to a real number. Maxine wrote: When I enter the vlookup formula you suggested, I get a value error. "Dave Peterson" wrote: It depends on what you mean by custom year format. =vlookup() and =match() don't care about what you see (how the cell is formatted), they care about the values that are in the cells. So if the table contained years (not real dates formatted as yyyy to just show the year), you could use a formula like: =vlookup(--a1,sheet2!a:b,2,false) --a1 will coerce the text in A1 to a real number. And then that real number can match one of the values in column A of sheet2. -A1 will make the text number a real number, but negative (well, if A1 looked like a positive number). --A1 will convert the negative number to a positive number. =============== If you really have dates in column A of sheet2, but you formatted the cells to just show the date, you could use a formula like: =index(sheet2!b1:b999,match(--a1,year(sheet2!a1:a999),0)) (this will match the coerced number in A1 to the year (a real number).) or =index(sheet2!b1:b999,match(a1,text(sheet2!a1:a999 ,"yyyy")),0)) (This will match the text in A1 with the text year. (=text() returns text, not numbers.)) These are an array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Maxine wrote: I think this depreciation project is going to be the death of me. I hope someone can help. In one column of the template the years are entered as text format, but column I need to use for the vlookup is formatted in a custom year. How can I get the formula to recognize the text format year is the same as the custom year format. I have spent an obscene amount of hours trying to figure this out. Any help would be appreciated. Thank you. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using a number as text to "lookup" a match formatted i
Okay, this is close! Here's what I'm looking at:
cell a35 has "2006" entered as a text. Range c14:c25 has 2002 through 2010 entered as a "yyyy" format. Range d14:d25 has the annual depreciation expense I need to place in b35. By using the formula you gave me for b35, index(c14:c25,match(a35,text(c14:d25,"yyyy"))0)) plus adding the curly brackets by ctrl-shift-enter, gives me a value error. What am I doing wrong? "Dave Peterson" wrote: It depends on what you mean by custom year format. =vlookup() and =match() don't care about what you see (how the cell is formatted), they care about the values that are in the cells. So if the table contained years (not real dates formatted as yyyy to just show the year), you could use a formula like: =vlookup(--a1,sheet2!a:b,2,false) --a1 will coerce the text in A1 to a real number. And then that real number can match one of the values in column A of sheet2. -A1 will make the text number a real number, but negative (well, if A1 looked like a positive number). --A1 will convert the negative number to a positive number. =============== If you really have dates in column A of sheet2, but you formatted the cells to just show the date, you could use a formula like: =index(sheet2!b1:b999,match(--a1,year(sheet2!a1:a999),0)) (this will match the coerced number in A1 to the year (a real number).) or =index(sheet2!b1:b999,match(a1,text(sheet2!a1:a999 ,"yyyy")),0)) (This will match the text in A1 with the text year. (=text() returns text, not numbers.)) These are an array formulas. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Maxine wrote: I think this depreciation project is going to be the death of me. I hope someone can help. In one column of the template the years are entered as text format, but column I need to use for the vlookup is formatted in a custom year. How can I get the formula to recognize the text format year is the same as the custom year format. I have spent an obscene amount of hours trying to figure this out. Any help would be appreciated. Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
Formula to count number of time stamps within a range in a column having dates formatted as "custom" | Excel Discussion (Misc queries) | |||
Text formatted as "General" in Excel 2002. How do I avoid that? | Excel Discussion (Misc queries) | |||
How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) |