My theory on using vlookup is to always specify false, unless for some reason I don't want an exact match, regardless of whether or not my table is sorted. And it works in all versions of Excel, at least 97 forward is what I'm familiar with. Christine -----Original Message----- From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On Behalf Of Donald C. Smith Sent: Sunday, January 19, 2003 12:00 PM To: Mso-Microsoft Office Newsgroup (E-mail) Subject: [mso] Re: VLOOKUP Question - want exact match only! Thanks Again!! 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 ************************************************************* ************************************************************* 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 *************************************************************