On Sep 17, 3:22*pm, Colleen10
wrote:
Actually I was able to make the formula work by playing around with it a bit
but interestingly in some cases it returned the full value of the "R invoice
but other times it just returned the "R" itself. *Looks like some of the
cells were formatted or maybe were not numeric value...it's ok, I can fix
these with a concatenate. *Thanks so much

"Burke" wrote:
On Sep 17, 2:02 pm, Colleen10
wrote:
I have one spreadsheet that shows the original invoice # of 003567457A and
new version spreadsheet that shows the new invoice is now 003567457AR.. *I'm
trying to find a formula like vlookup that will match the newer version
invoice with the older version invoice because it is "similar"

Assuming the invoice # is in A1 in the original and the new invoice is
in a table in C2:G20 and you want the value of the 3rd column

vlookup(A1&"R",C2:G20,3,false)

Burke

Colleen, glad you got it worked out, however be careful with your use
of $A:$A as a lookup value and A:A as a lookup column, those
references create at least 65536 rows each to look in for a match, if
you have a lot of vlookups it will seriously slow down Excel due to
the way it recalculates.

You may be limited on what you can do in the file you are looking in
(BRP MFG BILLING.xls) VLOOKUP('[BRP MFG BILLING.xls]Report1'!$A:
$A&"R",A:A,1,FALSE) however you should be able to change the 1st
argument to reference a single cell instead of $A:$A you should use
$A1 and copy the formula down VLOOKUP('[BRP MFG BILLING.xls]Report1'!
$A1&"R",A:A,1,FALSE). Also UNLESS you have or potentially could have
close to 65536 invoices in BRP MFG BILLING.xls I would reduce A:A (in
the 2nd argument) to fewer rows, for example if you have about 10,000
invoices change the 2nd argument to something like $A1:$A12,000 or a
named range if that is feasible, it will recalculate much quicker
since it doesn't refer to 65536 rows.

It all depends on how much data you have and YMMV.