Vlookup not finding matches

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

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.
 
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.
 
Just to add a little bit here

You posted
I have tried reformatting the
cells to ensure they are the same.
Click to expand...

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
 
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 said:
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.
Click to expand...
 
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.

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.
Click to expand...
 
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 matc
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 tha
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 th
worksheet with the formula

=b2=sheet2!a9

If you see true, then the =vlookup() should be working ok

But if you see False, then my bet is that you either
fiel

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 an
the numbers look the same--but because of the formatting (maybe hidin
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 suggestion
are on the mark

Andy wrote

--

Dave Peterson

Previous Posts In This Thread:

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

Debra Dalgleish shares some debugging techniques for the =vlookup()
Debra Dalgleish shares some debugging techniques for the =vlookup() formula
http://contextures.com/xlFunctions02.html#Troubl

Andy wrote

-

Dave Peterson

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

--
And

:

Just to add a little bit hereYou postedYou can't just reformat the cells, you
Just to add a little bit her

You poste

You cannot just reformat the cells, you have to CONVERT the
from text to numbers

You can read all about it at the link that Dave posted

HT
Martin

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 col
=A1&B1&C1 and copy dow
then in one sheet in E1 ente
=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 othe
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 :-

:

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 matc
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 tha
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 th
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/tutorial...8-e9e000e7c99a/build-an-iis-virtual-dire.aspx
 
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.
 
Back
Top