Vlookup not working #REF error

[vba317]

I have tried many different combinations with #REF, or #N/A errors for a result

I am trying to read from another sheet with no luck.
Currently I am on a sheet called results
The sheet that I am trying to get information from is called Source
Currently I am in cell D6 on the results sheet
What I am trying to do is if cell A6 is equal to anything on the source sheet between A2:A10470 I want the value in the cell from the C column on the source sheet to appear in cell D6 on the results sheet.

I am currently getting a #REF error
in this case cell A6 on the results sheet = 10180
Cell A191 on the source sheet = 10180 the value in cell C191 is 2.25 from the source sheet I would like 2.25 to be in cell D6 of the results sheet.

Any help would be appreciated
Tom

=VLOOKUP(A6,Source!A2:A10470,4)

 

[SkipVought]

Hi,

Did you look at HELP for what a #REF! Indicates?

Your REFRERNCE is out of bounds, cuz you've only referenced column A, the lookup column, but you want data from the fourth column of the reference range! TILT!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

The reason why you're getting an error is that your array is only 1 column. Try changing your code to the following:

=VLOOKUP(A6,Source!A2:C10470,3)

 

[xlbo]

Also - unless you intentionally want to change the lookup range, use $ to make the range relative rather than absolute source!$A$2:$C$10470

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[byundt]

VLOOKUP has an optional fourth parameter. The default value (what you get if you don't specify a value) is TRUE, which means that Source column A is sorted in ascending order and an exact match is not required. If you require an exact match for cell A6, you will want to specify FALSE as that fourth parameter:

=VLOOKUP(A6,Source!A$2:C$10470,3,FALSE)