Here is my problem: In Column A I have a list of 50 Invoice numbers that I show unpaid. In Column B I have the invoice numbers showing paid, however there is an additional set of numbers ranging from 8 to 12 in front of the numbers I need to match. Column A-- 43689149 (50 different numbers)

Column B--0015083702 42710914 (6900 different numbers)In essence I want to compare each value in A (in this example 43689149) and see if it matches the latter portion of column B, all 6900 numbers, ( in this example 42710914).

Any Suggestions??

Hi, Try the following:

Add a column to the right of Column A.

The 'long' paid numbers are now in column C - starting in C2 in my example

In column D alongside the first long number (D2 in my example), enter this formula:=RIGHT(C2,LEN(C2)-FIND(" ",C2,1))Drag this formula down alongside all of the long numbers. This will give you a list of the shorter numbers, ready for comparison.

In B2 enter this formula:=IF(ISERROR(VLOOKUP(TEXT(A2,"#"),$D$2:$D$12,1,FALSE)),"Not found","Found")Note the $ signs

Drag the formula down alongside the short numbers.The TEXT(A2,"#") part of the formula is because I am assuming the values in column A are numbers and not text. The values in column D that they are being compared to, are text.

Regards

A B C D

43689149 Not found 0011037140 43762353 43762353

43699441 Not found 0030204655 42827287 42827287

43753635 Not found 6800157733 42872276 42872276

43762353 Not found 1967619840 42911547 42911547

43762672 Not found 0060477569 42913459 42913459Heres the first few rows of my table and as you can see A4 is stating not found when it actually sits in D1. The ren command with find worked great and I had actually found that too, but came up with the same issue as you. Any tweaks? Thank you!

I found a solution: I copied the information you had me put into cell D and pasted it to cell E but as a paste special I then chose value only. After it copied it over I chose to convert it to numbers instead of text and then changed the formula to look for the array inside column E instead of D. Thanks so much for your help!!!

Hi, Glad it worked.

A couple of hints in case you do more Excel work like this:

1. =IF(ISERROR(VLOOKUP(A2,$D$2:$D$12,1,FALSE)),"Not found","Found")

Using this formula assumes that the data being tested is text. The original formula converted the data in column A from a number to text.

2.=VALUE(RIGHT(C2,LEN(C2)-FIND(" ",C2,1)))

This version of the formula that separated out the 'short' number, converts the text to a number.

3. Sometimes comparing text doesn't work because of spaces around the text - which you can't see. So this version =IF(ISERROR(VLOOKUP(TRIM(A2),$D$2:$D$12,1,FALSE)),"Not found","Found") uses the TRIM function to remove any spaces.

4. When comparing or looking up numbers, there may be minor differences between numbers way out in the last few decimal places, (which don't show in the cell), so use the ROUND() function to round both numbers down to fewer decimal places, and the minor differences will disappear.Regards

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History