[mso] Re: Comparable data formats in Excel VLOOKUP
- From: dave sharpe <pmmgpgp@xxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Tue, 03 Feb 2009 19:56:27 -0500
Peggy
VLookup( value, table_array, index_number, not_exact_match )
A thought, have you tried appending the apostrophe to the value
("'"&value)
VLookup( "'"&value, table_array, index_number, not_exact_match )
Dave
Peggy Knesebeck wrote: I am having trouble comparing values within a cell in
Excel when doing VLOOKUP. When we download data from our system into Excel,
account numbers load with green triangles in the upper left corner of the
cell. When I try to reformat the cell through the edit mode, it doesn't
change that flag, or change it to a format I can use to compare values. The
only way I can get it to work, is to go into the table that is being used as
the lookup table, and change those cells individually to text by inserting
anapostrophe in front of the value. (I am usually dealing with a large
volumeof data, so treating each cell individually in not realistic.) If I
tryto format it to text under the edit option, it still will not change it
toa format recognizable for the comparison purpose. I've copied a sample
below to demonstrate. You can see in D8, when I put an apostrophe in front
ofthe value, it gives the comparable green triangle and returns a lookup
value. The res t of the data in column D is formated as number, as is the
data in column A. However, they are not comparable. I have 2 questions: 1.
Isthere a way to reformat a large range of cells to a number format that
willbe recognizable for comparison purposes in a VLOOKUP? 2. Is there a way
to go into a large range of cells and insert an apostrophe in front of the
value in the lookup table, so they would be comparable in a VLOOKUP? Thanks
in advance for your help! Lookup Table Data VLOOKUP POCA FS Item POCA Amt
Return FSItem 3000000 130390 3000000 $ 1,500.00 130390 3000001 130390
3000000#N/A 3000002 130390 3020002 #N/A 3000003 130390 3020002 $ - #N/A
3000004 130390 3020002 $ 1,621,076.38 #N/A 3000005 130390 3020101 $ - #N/A
3000006 130390 3020101 $ 142,722.77 #N/A 3000007 130390 3050001 $ - #N/A
3000008 130390 3050001 $ 900,436.87 #N/A 3000009 130390 3050002 $ - #N/A
3000101 130390 3050002 $ 620,922.08 #N/A 3000102 130390 3050007 $
2,062,563.85 #N/A 3000103 130390 3050007 #N/A 3000108 130390 3050007 $ -
#N/A3000109 130390 3050007 $ 2,062,563.85 #N/A 3000113 130390 3090000 $
7,199.30 #N/A 3000114 130390 3090000 $ - #N/A 3000115 130390 3090000 $
9,842.00 #N/A 3000117 130390 3000120 130390 3000135 130390 3000136 130390
3000140 130390 *************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx[1]
orMicrosoftOffice@xxxxxxxxxxxxxxx[2]. To send mail to the group, simply
address it to mso@xxxxxxxxxxxxx[3] To Unsubscribe from this group, visit the
group's homepage and use the dropdown menu at the top. This will allow you
tounsubscribe your email address or change your email settings to digest or
vacation (no mail). //www.freelists.org/webpage/mso[4] To be able to
share files with the group, you must join our Yahoo sister group. This group
will not allow for posting of emails, but will allow you to join and share
problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice[5] . This group is for
FILE SHARING ONLY. If you are using Outlook and you see a lot of unnecessary
code in your email messages, read these instructions that explain why and
howto fix it: http://personal-computer-tutor.com/abc3/v28/greg28.htm[6]
*************************************************************
----------------------------------------------------------------------------
No virus found
in this incoming message. Checked by AVG - www.avg.com[7] Version: 8.0.233 /
Virus Database: 270.10.17/1932 - Release Date: 02/03/09 07:57:00
--- Links ---
1 mailto:mso@xxxxxxxxxxxxx
2 mailto:MicrosoftOffice@xxxxxxxxxxxxxxx
3 mailto:mso@xxxxxxxxxxxxx
4 //www.freelists.org/webpage/mso
5 http://tech.groups.yahoo.com/group/MicrosoftOffice
6 http://personal-computer-tutor.com/abc3/v28/greg28.htm
7 http://www.avg.com
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
