I have a VLOOKUP formula as follows
=VLOOKUP($B$2,NS2005!$B$2:$L$416,2)
where b2 is a result like Week1, Week2, etc...Week52)

When b2 results in Week1 everything works fine and returns the correct
dollar amount from column 2 of the range, but when it equals Week2,
Week3, Week4, Week5, Week6, Week7, Week8, or Week9 it returns 0's.

The strange thing is that it works right again starting with when b2
results in Week10 and every week thereafter.

I have checked every possible spelling and all the formulas and I
CANNOT find why it won't work for Weeks 2-9

Does anyone have any ideas?

Thanks
Dave

Should you not be using ,False for an exact match rather than ,2 for a
best guess?

--

DMDave Wrote:
I have a VLOOKUP formula as follows
=VLOOKUP($B$2,NS2005!$B$2:$L$416,2)
where b2 is a result like Week1, Week2, etc...Week52)

When b2 results in Week1 everything works fine and returns the correct
dollar amount from column 2 of the range, but when it equals Week2,
Week3, Week4, Week5, Week6, Week7, Week8, or Week9 it returns 0's.

The strange thing is that it works right again starting with when b2
results in Week10 and every week thereafter.

I have checked every possible spelling and all the formulas and I
CANNOT find why it won't work for Weeks 2-9

Does anyone have any ideas?

Thanks
Dave


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=539687

According to my understanding the ,2 refers to the column index of the
range referred to in the lookup, in this case the column would be C. I
could be wrong except that it works fine for most of the b1 results.

Thanks
Dave

On Sun, 7 May 2006 16:40:51 -0500, Bryan Hessey
wrote:


Should you not be using ,False for an exact match rather than ,2 for a
best guess?

--

DMDave Wrote:
I have a VLOOKUP formula as follows
=VLOOKUP($B$2,NS2005!$B$2:$L$416,2)
where b2 is a result like Week1, Week2, etc...Week52)

When b2 results in Week1 everything works fine and returns the correct
dollar amount from column 2 of the range, but when it equals Week2,
Week3, Week4, Week5, Week6, Week7, Week8, or Week9 it returns 0's.

The strange thing is that it works right again starting with when b2
results in Week10 and every week thereafter.

I have checked every possible spelling and all the formulas and I
CANNOT find why it won't work for Weeks 2-9

Does anyone have any ideas?

Thanks
Dave

after the 2 which tells which column to lookup, you could use false to
find an exact match or true to find the closest

=VLOOKUP($B$2,NS2005!$B$2:$L$416,2,false)

DMDave Wrote:
I have a VLOOKUP formula as follows
=VLOOKUP($B$2,NS2005!$B$2:$L$416,2)
where b2 is a result like Week1, Week2, etc...Week52)
Thanks
Dave

You need a false after 2
=VLOOKUP($B$2,NS2005!$B$2:$L$416,2,FALSE)


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=539687

I figured out what was wrong. The Week1, Week2....Week52 sorts
ascending to Week1, Week10, Week11...Week2, Week20, Week21....etc.

The problem was fixed by changing Week1 to 1Week1 etc.... That keeps
the order correct.

Thanks for your suggestions which helped me discover the real problem.

Dave

On 7 May 2006 15:01:10 -0700, "damorrison"
wrote:

after the 2 which tells which column to lookup, you could use false to
find an exact match or true to find the closest

=VLOOKUP($B$2,NS2005!$B$2:$L$416,2,false)

Apart from my mis-reading the question, if you are looking to select the
price for the week then it is probably safer to use ,2,False which
requests an exact match rather than use a correct sort sequence without
the ,False which will give you the answer from the 'best guess' (ie,
closest to a match but not greater than).

also, thank you for responding to those who helped you.

--

DMDave Wrote:
I figured out what was wrong. The Week1, Week2....Week52 sorts
ascending to Week1, Week10, Week11...Week2, Week20, Week21....etc.

The problem was fixed by changing Week1 to 1Week1 etc.... That keeps
the order correct.

Thanks for your suggestions which helped me discover the real problem.

Dave

On 7 May 2006 15:01:10 -0700, "damorrison"
wrote:

after the 2 which tells which column to lookup, you could use false
to
find an exact match or true to find the closest

=VLOOKUP($B$2,NS2005!$B$2:$L$416,2,false)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=539687