alternate vlookup method?

  • Thread starter Thread starter jrw
  • Start date Start date
J

jrw

I am trying to use vlookup to match an invoice number in one file to a
invoice number in another file and retrieve the credit# for it. Th
problem is that the invoice has been credited by multiple credits s
the invoice is also shown numerous times. Vlookup will go to the firs
invoice occurrence and retrieve the corresponding credit #. Is ther
any way to get the credit #'s associated with an invoice
 
In your source file from which you pull the credit #,
you'll have to have two more columns giving the sequence
number of the duplicate invoice nos and an Invoice Key.
For eg: If 1234 is the invoice no, the file will look like
this:

Invoice No Sequence No Invoice Key
---------- ----------- -----------
1234 0 =concatenate(Invoice No,Sequence N0)
1234 1 =concatenate(Invoice No,Sequence N0)
1234 2 =concatenate(Invoice No,Sequence N0)
1234 3 =concatenate(Invoice No,Sequence N0)

You'll have to form the invoice key for both your source
and destination files and then do the vlookup based on the
invoice key.

If you want to avoid the formation of the sequence no
field, you need to identify any field that would be unique
to that individual invoice #s. And then form the key based
on this field.


Joseph
 
Back
Top