Vlookup gives me #ref error

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,021
I have a list of unique numbers (assignment numbers) on a work sheet, this list SHOULD match another worksheet I have open . . but they don't always, and I want to know what the value is in the column that should match (column B).

So I did a Vlookup . ....

=VLOOKUP(A35,'[December EXPORT HTS TRACKER.xls]Export Tracker'!$A$2:$A$500,2,0)

(A35 is the first row on my spreadsheet where this function lives. Column A on both worksheets is the same value, both sorted ascending.)

I get a Ref# error . . . .if I change the formula to ...$A$2:$A$500,1,0 it returns the correct assignment #, and all cells are populated.

thoughts?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You're lookup range needs to be at least as many columns wide as your colref # (2).

so your range should cover columns A & B (2 columns) since you put 2 as the colref.

=VLOOKUP(A35,'[December EXPORT HTS TRACKER.xls]Export Tracker'!$A$2:$B$500,2,0)

If colref was 3, it would have to be

=VLOOKUP(A35,'[December EXPORT HTS TRACKER.xls]Export Tracker'!$A$2:$C$500,3,0)
 
Upvote 0
Because your only looking at column 'A'. When you do (,2,false) you are looking in the 2nd column which is B. You will get a ref error. When using vlookup your column that you look for the data in should be as many columns in your lookup table.


So, if you do vlookup(value,A1:A500,1,false) this is OK, then if you want the 2nd column you have to do (value, A1:B500,2,false)
 
Upvote 0
The column index for VLOOKUP is 2, yet the lookup table A2:A500 only consists of only one column, which necessarily returns #REF!. If you'd like to return the corresponding value in Column B, change...

$A$2:$A$500

to

$A$2:$B$500

Hope this helps!
 
Upvote 0
AHHHH, duh, I knew that after I read your reply twice!! I guess I should have asked my self, "how can I find a SECOND column, if i am only searching ONE columb ($A2:A500). when I changed the A500 to L500, it works fine. THANKS!!!! I love you guys!!!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top