• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting Error as "#REF!" while using VLOOKUP formula

srikanthn072

New Member
I want to use VLOOKUP() excel formula in my Excel workbook.
I used VLOOKUP () formula as =VLOOKUP(A2,TLDL!C:C,2,0)
I wanted the excel to lookup "A2" value in coloumn "C" of TLDL worksheet and wanted excel to return the corresponding value in 2nd coloumn.
The system has returned a error as "#REF!"
Can any one explain me where i have done a mistake?
 
srikanthn072
As You're a new member, You would remember from Forum Rules
The 1st line from Please Don't
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
After those ... of course, You have tried to study someway about vlookup
and find like:
The #REF! error shows when a formula refers to a cell that’s not valid .
This happens most often when cells that were referenced by formulas get deleted, or pasted over.

Ps Forum Rules has part named How to get the Best Results at Chandoo.org
... For the best/fastest results, Upload a Sample File
Because, there will be challenges for other to guess ... what do You really have somewhere?
 
i have tried a number of ways but unable to find solution for the above mentioned vlookup() syntax.
the above same syntax is working to pull out data from other worksheet by name "AUCA" when given cell range as "1:10000".
previous formula =VLOOKUP(A2,TLDL!C:C,2,0) is returning "#REF!" and hence not working.
present formula =VLOOKUP(A2,AUCA!1:10000,2,0) is able to pull out the corresponding values from that worksheet.
Can anyone please explain where the difference is and how can i pull out data from worksheet "TLDL" as well.
 
srikanthn072
As You're a new member, You would remember from Forum Rules
The 1st line from Please Don't
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
After those ... of course, You have tried to study someway about vlookup
and find like:
The #REF! error shows when a formula refers to a cell that’s not valid .
This happens most often when cells that were referenced by formulas get deleted, or pasted over.

Ps Forum Rules has part named How to get the Best Results at Chandoo.org
... For the best/fastest results, Upload a Sample File
Because, there will be challenges for other to guess ... what do You really have somewhere?
i have tried a number of ways but unable to find solution for the above mentioned vlookup() syntax.
the above same syntax is working to pull out data from other worksheet by name "AUCA" when given cell range as "1:10000".
previous formula =VLOOKUP(A2,TLDL!C:C,2,0) is returning "#REF!" and hence not working.
present formula =VLOOKUP(A2,AUCA!1:10000,2,0) is able to pull out the corresponding values from that worksheet.
Can anyone please explain where the difference is and how can i pull out data from worksheet "TLDL" as well.
 
srikanthn072
There are more ways to do this the way
that it would give other than wanted result.

Did You read my two last lines?
For the best/fastest results, Upload a Sample File
Because, there will be challenges for other to guess ... what do You really have somewhere?
 
Use the solution in #3.
In the first case your referenced range is 10,000 rows by 16,384 columns exactly. That sounds pretty large to me but if that is what your data requires so be it. There is no problem in returning data from the 2nd column of such a range. In the second formula you have referenced a single column and then requested the result to be returned from the 2nd column of a 1 column range. This causes a bit of a problem.
 
I thank you all for the responses. I will be back with a sample file so that you all can better know the causes of the errors i am getting. Thank you.
 
Hi all. Please find the attached sample file and guide me all the correct ways (syntax) i can use of vlookup() formula to get desired results.
 

Attachments

  • Sample File.xlsx
    99.8 KB · Views: 6
srikanthn072
rows 14...22 : There are not those account nos
rows 26...34 : eg for C26 =VLOOKUP(B26,CCOD!A:D,3,0) gives answers
... why do You want to use vlookup with so many columns?
Please, reread its syntax.
 
The problem is as others have been telling you. The second argument of a VLOOKUP is a Table that includes both the data to search and the data to return, not just a column.

I would suggest that you investigate the possibility of converting to using Excel Tables which provide precise ways of referencing the data you need.
 
srikanthn072
rows 14...22 : There are not those account nos
rows 26...34 : eg for C26 =VLOOKUP(B26,CCOD!A:D,3,0) gives answers
... why do You want to use vlookup with so many columns?
Please, reread its syntax.
Thank you so much. Now i got the clarity about how to give the second argument in the vlookup() formula.
 
The problem is as others have been telling you. The second argument of a VLOOKUP is a Table that includes both the data to search and the data to return, not just a column.

I would suggest that you investigate the possibility of converting to using Excel Tables which provide precise ways of referencing the data you need.
Thank you so much. Now i got the clarity about how to give the second argument in the vlookup() formula.
 
Back
Top