VLOOKUP using a number as text to "lookup" a match formatted in a

[Maxine]

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]

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]

When I enter the vlookup formula you suggested, I get a value error.

 

[Dave Peterson]

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]

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?