Strange VLOOKUP Problem

[DMDave]

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]

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

--

 

[DMDave]

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

 

[damorrison]

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)

 

[davesexcel]

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)

 

[DMDave]

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

 

[Bryan Hessey]

Apart from my mis-reading the question, if you are looking to select th
price for the week then it is probably safer to use ,2,False whic
requests an exact match rather than use a correct sort sequence withou
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.

--