[mso] Re: VLOOKUP Question - want exact match only! Thanks Again!!
- From: "Donald C. Smith" <DCSmith@xxxxxxxxxxxx>
- To: "Mso-Microsoft Office Newsgroup (E-mail)" <mso@xxxxxxxxxxxxx>
- Date: Sun, 19 Jan 2003 15:00:19 -0500
It figures that it would make a difference in this case. I have Excel 2000
with Windows 2000. I do have XP also but it needs to be compatible with
both versions. My lookup is in descending order as I used the sort function
when I set it up. I am assuming from what you said that what you have
described (keep in mind I am not a rocket scientist) will not work in 2000.
If it will maybe you can explain it in SIMPLE American English. Remember
the AVERAGE person can only hold an average of seven pieces of information
in short term memory for only a minute or two. Hopefully the formula I have
at the bottom of the page will simplify things (for all, especially me).
Thanks Again
Don
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
You didn't specify your version of Excel so I'll go ahead and submit
that there's a fourth argument you can supply to VLOOKUP in Excel XP
called Range_Lookup. This argument allows you to specify exact matches
or a match within the range. Here are the listed arguments/definitions
from MSKB article 324985 :
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value is the value to be found in the first column of the array;
lookup_value can be a value, a reference, or a text string.
Table_array is the table of information in which data is looked up. Use
a reference to a range or a range name, such as "Database" or "List."
If range_lookup is TRUE, you must put the values in the first column of
table_array in ascending order: ... -2, -1, 0, 1, 2 ... , A-Z, FALSE,
TRUE. Otherwise VLOOKUP may not return the correct value. If
range_lookup is FALSE, table_array does not have to be sorted.
You can put the values in ascending order by clicking the Sort command
on the Data menu and then selecting Ascending.
The values in the first column of table_array can be text, numbers, or
logical values.
Text is not case-sensitive.
Col_index_num is the column number in table_array from which the
matching value must be returned. A col_index_num of 1 returns the value
in the first column in table_array; a col_index_num of 2 returns the
value in the second column in table_array, and so on. If col_index_num
is less than 1, VLOOKUP returns the #VALUE! error value. If
col_index_num is greater than the number of columns in table_array,
VLOOKUP returns the #REF! error value.
Range_lookup is a logical value that specifies whether you want VLOOKUP
to find an exact match or an approximate match. If the Range_lookup
value is TRUE or omitted, an approximate match is returned. In other
words, if an exact match is not found, the next largest value that is
less than lookup_value is returned. If the Range_lookup value is FALSE,
VLOOKUP will find an exact match. If an exact match is not found, the
#N/A error value is returned.
Greg Chapman
http://www.mousetrax.com
"Counting in binary is as easy as 01, 10, 11!
With thinking this clear, is coding really a good idea?"
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx
> [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf Of Donald C. Smith
> Sent: Sunday, January 19, 2003 1:06 PM
> To: Mso-Microsoft Office Newsgroup (E-mail)
> Subject: [mso] VLOOKUP Question - want exact match only!
> Thanks Again!!
>
>
> I have a VLOOKUP formula that probably works as it is
> supposed to, but it returns the closest value. I don't want
> it to return any value unless the match is exact. I would
> once again appreciate help. Thanks Don
>
> =VLOOKUP(B2,$N$2:$O$40,2)
*************************************************************
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, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
