| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup not finding matches
I have two data sets each 3 columns wide, I am trying to match the data in
the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup not finding matches
Debra Dalgleish shares some debugging techniques for the =vlookup() formula:
http://contextures.com/xlFunctions02.html#Trouble Andy wrote: I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup not finding matches
Dave,
Thank you for responding and so quickly. The link was helpful in that I was not familiar with that site. I had done all of the things that Debra mentioned, previously, and they did not fix the problem. -- Andy "Dave Peterson" wrote: Debra Dalgleish shares some debugging techniques for the =vlookup() formula: http://contextures.com/xlFunctions02.html#Trouble Andy wrote: I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup not finding matches
Just to add a little bit here
You posted I have tried reformatting the cells to ensure they are the same. You can't just reformat the cells, you have to CONVERT them from text to numbers. You can read all about it at the link that Dave posted. HTH Martin "Andy" wrote in message ... I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup not finding matches
Which columns are you matching?
Assuming both sheets have Col A,B and C... are you matching on A&B&C? if yes then have this in both D cols =A1&B1&C1 and copy down then in one sheet in E1 enter =VLookup(D1,Sheet1!D:D,1,False) If they still don't match then paste the formats from one sheet to the other by selecting the whole sheet and painting the other Next copy row 1 from sheet 1 to the row 1 of sheet 2... They should match then... :-) It might tell you what was wrong... or just send the file to me :-) "Andy" wrote: Dave, Thank you for responding and so quickly. The link was helpful in that I was not familiar with that site. I had done all of the things that Debra mentioned, previously, and they did not fix the problem. -- Andy "Dave Peterson" wrote: Debra Dalgleish shares some debugging techniques for the =vlookup() formula: http://contextures.com/xlFunctions02.html#Trouble Andy wrote: I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup not finding matches
If you did all that that site suggests, then you really don't have a match
between the lookup value and the first column in the lookup range. If you think you actually do, then find an empty cell and plop in a formula that compares the cells you think match. Say your =vlookup() formula is: =vlookup(b2,sheet2!a:b,2,false) and you think that the value in Sheet2!A99 matches the value in B2 of the worksheet with the formula. =b2=sheet2!a99 If you see true, then the =vlookup() should be working ok. But if you see False, then my bet is that you either: #1. Have a number in one cell and text that looks like a number in the other field #2. Have extra space (or white space) characters in the cells Try this in a few of empty cells: =isnumber(b2) =isnumber(sheet2!a99) =len(b2) =len(sheet2!a99) Another problem you may have is that you could have a number in both cells and the numbers look the same--but because of the formatting (maybe hiding decimals), you don't see the real difference. If I were you, I'd review Debra's notes once more. I'd bet that her suggestions are on the mark. Andy wrote: Dave, Thank you for responding and so quickly. The link was helpful in that I was not familiar with that site. I had done all of the things that Debra mentioned, previously, and they did not fix the problem. -- Andy "Dave Peterson" wrote: Debra Dalgleish shares some debugging techniques for the =vlookup() formula: http://contextures.com/xlFunctions02.html#Trouble Andy wrote: I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching, but not finding
Hi Dave,
I tried matching, as you suggested, to see if it returns TRUE or FALSE. The check returns TRUE, however vlookup is not finding it. Any suggestions would be helpful. Dave Peterson wrote: If you did all that that site suggests, then you really don't have a 24-Oct-08 If you did all that that site suggests, then you really don't have a match between the lookup value and the first column in the lookup range. If you think you actually do, then find an empty cell and plop in a formula that compares the cells you think match. Say your =vlookup() formula is: =vlookup(b2,sheet2!a:b,2,false) and you think that the value in Sheet2!A99 matches the value in B2 of the worksheet with the formula. =b2=sheet2!a99 If you see true, then the =vlookup() should be working ok. But if you see False, then my bet is that you either: field Try this in a few of empty cells: =isnumber(b2) =isnumber(sheet2!a99) =len(b2) =len(sheet2!a99) Another problem you may have is that you could have a number in both cells and the numbers look the same--but because of the formatting (maybe hiding decimals), you don't see the real difference. If I were you, I'd review Debra's notes once more. I'd bet that her suggestions are on the mark. Andy wrote: -- Dave Peterson Previous Posts In This Thread: On Friday, October 24, 2008 8:47 PM And wrote: Vlookup not finding matches I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy On Friday, October 24, 2008 8:55 PM Dave Peterson wrote: Debra Dalgleish shares some debugging techniques for the =vlookup() Debra Dalgleish shares some debugging techniques for the =vlookup() formula: http://contextures.com/xlFunctions02.html#Trouble Andy wrote: -- Dave Peterson On Friday, October 24, 2008 9:20 PM And wrote: Dave,Thank you for responding and so quickly. Dave, Thank you for responding and so quickly. The link was helpful in that I was not familiar with that site. I had done all of the things that Debra mentioned, previously, and they did not fix the problem. -- Andy "Dave Peterson" wrote: On Friday, October 24, 2008 9:34 PM MartinW wrote: Just to add a little bit hereYou postedYou can't just reformat the cells, you Just to add a little bit here You posted You cannot just reformat the cells, you have to CONVERT them from text to numbers. You can read all about it at the link that Dave posted. HTH Martin On Friday, October 24, 2008 10:13 PM ="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote: Which columns are you matching? Which columns are you matching? Assuming both sheets have Col A,B and C... are you matching on A&B&C? if yes then have this in both D cols =A1&B1&C1 and copy down then in one sheet in E1 enter =VLookup(D1,Sheet1!D:D,1,False) If they still don't match then paste the formats from one sheet to the other by selecting the whole sheet and painting the other Next copy row 1 from sheet 1 to the row 1 of sheet 2... They should match then... :-) It might tell you what was wrong... or just send the file to me :-) "Andy" wrote: On Friday, October 24, 2008 10:13 PM Dave Peterson wrote: If you did all that that site suggests, then you really don't have a If you did all that that site suggests, then you really don't have a match between the lookup value and the first column in the lookup range. If you think you actually do, then find an empty cell and plop in a formula that compares the cells you think match. Say your =vlookup() formula is: =vlookup(b2,sheet2!a:b,2,false) and you think that the value in Sheet2!A99 matches the value in B2 of the worksheet with the formula. =b2=sheet2!a99 If you see true, then the =vlookup() should be working ok. But if you see False, then my bet is that you either: field Try this in a few of empty cells: =isnumber(b2) =isnumber(sheet2!a99) =len(b2) =len(sheet2!a99) Another problem you may have is that you could have a number in both cells and the numbers look the same--but because of the formatting (maybe hiding decimals), you don't see the real difference. If I were you, I'd review Debra's notes once more. I'd bet that her suggestions are on the mark. Andy wrote: -- Dave Peterson Submitted via EggHeadCafe - Software Developer Portal of Choice Build an IIS Virtual Directory Addin for VS.NET http://www.eggheadcafe.com/tutorials...tual-dire.aspx |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching, but not finding
You may want to describe your data and what you did.
If you did all that Debra suggested on her site, then I don't have a guess. john, bob wrote: Hi Dave, I tried matching, as you suggested, to see if it returns TRUE or FALSE. The check returns TRUE, however vlookup is not finding it. Any suggestions would be helpful. Dave Peterson wrote: If you did all that that site suggests, then you really don't have a 24-Oct-08 If you did all that that site suggests, then you really don't have a match between the lookup value and the first column in the lookup range. If you think you actually do, then find an empty cell and plop in a formula that compares the cells you think match. Say your =vlookup() formula is: =vlookup(b2,sheet2!a:b,2,false) and you think that the value in Sheet2!A99 matches the value in B2 of the worksheet with the formula. =b2=sheet2!a99 If you see true, then the =vlookup() should be working ok. But if you see False, then my bet is that you either: field Try this in a few of empty cells: =isnumber(b2) =isnumber(sheet2!a99) =len(b2) =len(sheet2!a99) Another problem you may have is that you could have a number in both cells and the numbers look the same--but because of the formatting (maybe hiding decimals), you don't see the real difference. If I were you, I'd review Debra's notes once more. I'd bet that her suggestions are on the mark. Andy wrote: -- Dave Peterson Previous Posts In This Thread: On Friday, October 24, 2008 8:47 PM And wrote: Vlookup not finding matches I have two data sets each 3 columns wide, I am trying to match the data in the first set to the second set. Most of the time the vlookup formula I am using works fine, however, it does not work all the time. There are data elements in the sets that are not matching. I have tried reformatting the cells to ensure they are the same. I have the fourth argument set to FALSE to find an exact match. I believe I have tried everything I can think of and am very frustrated. Any help would be most appreciated. -- Andy On Friday, October 24, 2008 8:55 PM Dave Peterson wrote: Debra Dalgleish shares some debugging techniques for the =vlookup() Debra Dalgleish shares some debugging techniques for the =vlookup() formula: http://contextures.com/xlFunctions02.html#Trouble Andy wrote: -- Dave Peterson On Friday, October 24, 2008 9:20 PM And wrote: Dave,Thank you for responding and so quickly. Dave, Thank you for responding and so quickly. The link was helpful in that I was not familiar with that site. I had done all of the things that Debra mentioned, previously, and they did not fix the problem. -- Andy "Dave Peterson" wrote: On Friday, October 24, 2008 9:34 PM MartinW wrote: Just to add a little bit hereYou postedYou can't just reformat the cells, you Just to add a little bit here You posted You cannot just reformat the cells, you have to CONVERT them from text to numbers. You can read all about it at the link that Dave posted. HTH Martin On Friday, October 24, 2008 10:13 PM ="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote: Which columns are you matching? Which columns are you matching? Assuming both sheets have Col A,B and C... are you matching on A&B&C? if yes then have this in both D cols =A1&B1&C1 and copy down then in one sheet in E1 enter =VLookup(D1,Sheet1!D:D,1,False) If they still don't match then paste the formats from one sheet to the other by selecting the whole sheet and painting the other Next copy row 1 from sheet 1 to the row 1 of sheet 2... They should match then... :-) It might tell you what was wrong... or just send the file to me :-) "Andy" wrote: On Friday, October 24, 2008 10:13 PM Dave Peterson wrote: If you did all that that site suggests, then you really don't have a If you did all that that site suggests, then you really don't have a match between the lookup value and the first column in the lookup range. If you think you actually do, then find an empty cell and plop in a formula that compares the cells you think match. Say your =vlookup() formula is: =vlookup(b2,sheet2!a:b,2,false) and you think that the value in Sheet2!A99 matches the value in B2 of the worksheet with the formula. =b2=sheet2!a99 If you see true, then the =vlookup() should be working ok. But if you see False, then my bet is that you either: field Try this in a few of empty cells: =isnumber(b2) =isnumber(sheet2!a99) =len(b2) =len(sheet2!a99) Another problem you may have is that you could have a number in both cells and the numbers look the same--but because of the formatting (maybe hiding decimals), you don't see the real difference. If I were you, I'd review Debra's notes once more. I'd bet that her suggestions are on the mark. Andy wrote: -- Dave Peterson Submitted via EggHeadCafe - Software Developer Portal of Choice Build an IIS Virtual Directory Addin for VS.NET http://www.eggheadcafe.com/tutorials...tual-dire.aspx -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding subsequent matches | Excel Worksheet Functions | |||
| Finding matches ( | Excel Discussion (Misc queries) | |||
| Finding matches (in Excel 2007) | Excel Discussion (Misc queries) | |||
| finding exact matches | Excel Worksheet Functions | |||
| finding exact matches using vlookup | Excel Worksheet Functions | |||
Linear Mode
