Formatting? Causing Problems with Vlookup (2000)

Topic

New Reply

Hi, I am using a Vlookup to try and determine matches. Apparently the way the numbers are formatted is causing this not to work. I have tried to clear the formatting then formatting both sets of numbers as either numerical or text data to no avail as well as removing all spaces. I am attaching a list of the two sets the numbers ( one contains many more numbers than the other). Any ideas on how to fix this such that I don’t have this problem and a match is indeed a match????? For example, each column has 1005454 in it but don’t show up as a match using vlookup.

Thanks in advance.

Reply | Quote

Replies

You don’t explain how (and why) you use VLOOKUP. I have no problem matching the values in the workbook you attached. What are you trying to accomplish?

Reply | Quote

I had no problem looking up any of the values in Column A in Column B using VLOOKUP and returning a value that I put in column C. Could you include an example of the problem you are having?

Reply | Quote

Reply to Hans and Legare, I have data that may have passed into one system in one month; however, does not pass into the other system until another month. What I do in my monthly reconciliation is identify the “timing differences.” Consequently, I use a Vlookup to look at the invoice numbers (what I attached on the spreadsheet) to look between the 2 sets of data. When a match occurs, I like to return some verbiage, such as “Previous Month.” So, I used this Vlookup process with a “false” in the last field looking for exact matches. There should have been 39 matches if memory serves me correctly; however, there were only 2. I try to clear the formatting, then format both as numerical, text, etc. and seems like it is impossible to get these things to “match” using the Vlookup criteria.

In short, how can a determine how a cell is formatted and how can I, with assurance, determine that 2 cells have identical formatting?

Thanks for the help!

Reply | Quote

See attached zipped workbook; it uses MATCH instead of VLOOKUP, since you only want to know if there is a match, you don’t need to look up something in another column.

Reply | Quote

To determine if the value in a cell is text or number, you can use a formula like this:

=IF(ISTEXT(A1),"Text","Number")

However, you also need to know that just changing the format of a cell that already contains a value from Text to Numeric or from Numeric to Text will NOT convert the value in the cell from one to the other. You have to change the format and then do something to change the format of what is there. Exactly what you do depends on what you are expecting to happen.

Reply | Quote

What formatting are you interested in:
Number format, bolding, underlining, italics, color, etc?

All would require VB to determine. A possible snag is that individual characters have formatting also so the cell may be “neither” (bold, Italics, underline) or have “multiple colors”. Do yu need to compare all formatting?

Steve

Reply | Quote