TEXT() formats numbers into text. It is a standard Excel function. The "0"
is a format string ... in this case, a minimum of one numeric character in
the result ... but format strings can get more complex than that when
required.
Regards, Dave S
----- Original Message -----
From: "Manny" <mgarza@fellowes.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Thursday, August 18, 2011 2:17 AM
Subject: [ExcelVBA] Re: Excel VLookup - doesn't work
> Dave,
> Your solution below is probably the most accurate from the responses I've
> seen. I had this issue many times when I first began using vlookups from
> data imported into Excel. Anyhow...I did not know it was possible to
> change the format of a cell as it goes to the Vlookup. In the example you
> posted below what argument is being used after the cell refernce? Or asked
> differently why are you calling out "o"? i.e. vlookup(TEXT(C2,"O")...
> Is that a zero or letter o?
>
> Thanks!
>
> --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...>
> wrote:
>>
>> 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@...>
>> 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
>> >
>>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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/3839 - Release Date: 08/16/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