Are you trying to do a look up based on column C in both sheets?
You have
=VLOOKUP(C2,sheet2!$A$2:$C$10248,3,FALSE)
which is taking C2 and trying to find it in column A of the other sheet.
VLookUp looks in the left-most column only. You can use Match() and Index()
to find information to the left of a search column. I think other replies
have already given you that.
Regards, Dave S
----- Original Message -----
From: "Sharron Puryear" <sheizageek@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Saturday, August 20, 2011 6:09 AM
Subject: Re: [ExcelVBA] Excel VLookup - doesn't work
Column C is actually alpha-numeric, i.e. ABC-1234. I even tried copying and
pasting values of the three main columns to a new workbook. It still doesn't
work. Below is the structure of both worksheets. Thank you for your time and
patience.
Column A Column B Column C
1 1234 ABC-1234
2 1235 DEF-5678
Thanks
Sharron
From: David Smart <smartware.consulting@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Tuesday, August 16, 2011 5:15 PM
Subject: Re: [ExcelVBA] Excel VLookup - doesn't work
You will probably find that the value in C is numeric and the one in Sheet2
A is a string. This is common in cases where information has been imported
from an external system (e.g. database).
You'd need to change the format of C2 as it goes to the VLookUp. E.g.
=VLOOKUP(TEXT(C2,"0"),sheet2!$A$2:$C$10248,3,FALSE)
You might need to change the format string if leading zeroes are to be
included as well.
Could also be vice versa, of course, in which case it would be VALUE(C2)
instead.
Regards, Dave S
----- Original Message -----
From: "sheizageek" <sheizageek@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, August 17, 2011 12:49 AM
Subject: [ExcelVBA] Excel VLookup - doesn't work
>I have two worksheets. Each has multiple columns, but the same column A
>(ID) and Column C (Claim#). The first worksheet has 324 records and the
>second worksheet has 10,248 records. I want to search for each claim # in
>sheet1 to see if it exists in sheet2, and, if so, tell me the ID of the
>second sheet. This is the formula that I used, but I only get the #N/A
>error in every cell. =VLOOKUP(C2,sheet2!$A$2:$C$10248,3,FALSE)
>
> I know that there are matches, but it doesn't show anything but the
> error..
>
> Is this formula wrong? Does VLOOKUP search multiple worksheets/workbooks?
> Should I do this using VBA instead? If so, what code?
>
> Any assistance is appreciated.
>
> Sharron Puryear
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1392 / Virus Database: 1520/3836 - Release Date: 08/15/11
>
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1392 / Virus Database: 1520/3844 - Release Date: 08/19/11
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar