vlookup

[Jim]

Hello,

I am using the following vlookup formula: =VLOOKUP(A2,SF_Input!A:I,10,FALSE)

however i am receiving an error: #REF!

I have also tried the following: =VLOOKUP(A2,SF_Input!A$1:I$100,10,FALSE)

I have double checked the fields and everything looks okay to me.

Thoughts? Is hte formula correct?

Thanks

 

[cm]

Jim,

couple of things -- your 'database' columns A thru I -- be sure the sheet
name is spelled correctly (SF_Input) also you probably don't need the $
absolute references there. However, I think your issue is that your are
selecting column 10 to find the value in, and there are only 9 columns in A
thru I.

 

[JLatham]

I suspect that the worksheet name is wrong. It probably looks like "SF_Input"
on the tab, and you think it's "SF_Input" and you have used that. But if you
double click the sheet's name tab, I suspect you're going to find that it has
some 'white space' at either the front or rear end of it as " SF_Input",
"SF_Input " or perhaps even " SF_Input ".

I believe if you get the sheet name cleaned up, your problem will go away.

 

[Mike H]

Jim,

You trying to return the tenth column from a 9 column lookup vector.

Mike

 

[Jim]

You're right I was only referencing 9 columns.

Thanks

 

[Gord Dibben]

Is that an I as in "eye" or an L as in "ell"?

If an "eye" then you have less than 10 columns in your lookup table so your
column index of 10 is out of range.


Gord Dibben MS Excel MVP

 

[MParham]

I don't know if this is your problem but I have run into similar issues in
the past. One thing I found sometimes fixes this, when everything looks
right, is to use the "Text to Columns" command on the source (column A on
your source) and again in the compare line (column A on your SF_Input sheet)
in the table.

I set the "Text to Column" to "Delimited" and then turn everything off on
the next screen and click "Finish". Do it in both places. I am not sure why
that is necessary but it often fixes my problem.

I noticed that I usually have to do this more often if the data is imported
from another program or database.