VLOOKUP ERROR

Chard9401

Board Regular
Joined
Jan 7, 2004
Messages
53
In cell A1 i enter a number between 1-40. Now depending on what number is entered will effect the results on my table.

I have a table with 4 headings. Position, Name, Team Name, and Points. Now the cells under the points column is worked out by a VLOOKUP. The VLOOKUP i use is =VLOOKUP(B1,Weeks,7,FALSE) now when i sort this table into numerical order it does not display the correct points and displays #REF! and the VLOOKUP has changed to =VLOOKUP(#REF!,Weeks,7,FALSE)

Why is this? Is any one able to help? If you need anymore info, let me know
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

crimlet

Board Regular
Joined
May 2, 2002
Messages
136
Have you moved or deleted cell B1 @ any time ? if so this results in a #REF!
 

Chard9401

Board Regular
Joined
Jan 7, 2004
Messages
53

ADVERTISEMENT

i have gone over it and retyped B1 but i get the same error again once i sort the table into order.

I haven't moved cell B1 it has a drop down list of 1 to 40 to select from and the depending on which number i choose will effect the results in the table. Now the error only occurs when the table is sorted into order. Is there any way i can prevent this from happening?
 

Chard9401

Board Regular
Joined
Jan 7, 2004
Messages
53
it also changes it to =VLOOKUP(B2,Weeks,11,FALSE) and get the error #N/A why is it changing my VLOOKUP formula? it never stays the same! i have a VLOOKUP on another sheet and that works fine, why is it i keep getting this error?
 

Chard9401

Board Regular
Joined
Jan 7, 2004
Messages
53
could i have a multiple IF statement? =IF(B1=1,C38,IF(B1=2,D38,IF(B2=3,E38))) but all the way up to IF(B1=40 if you know what i am trying to do. I could do it this way couldn't I? i really need some help with this
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,113
Members
425,261
Latest member
JW00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top