I have two worksheets. Worksheet 1 is the Detail AP Listing which has the vendor #, Invoice number and the description of what the invoice is for. Worksheet 2 is the GL which has the vendor # and the invoice number but not the description. I need to populate the Description of the invoice in the GL. THERE ARE DUPLICATE INVOICE NUMBERS so the Vlookup doesn't work.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Extracting Data from a secondary worksheet if the vendor and invoice match
- Thread starter leighnea
- Start date
Have you tried Index-Match formulas as an array?
Smething like this
change sheets and colums as you wish..
That will find a row where the two criterias needing to match, then input the description that follows the vendor and invoice number. so a double match.
Important!!! to make this work at all you need to use ctrl+shift+enter when you use the formula, or it will give you a value!/!NA! error.
Getting a snapshot of your two worksheets would help tho.
If you need help setting it up just ask
Smething like this
Code:
=INDEX(Sheet2!C:C;MATCH(Sheet1!C9&Sheet1!D9;Sheet2!A:A&Sheet2!B:B;0))change sheets and colums as you wish..
That will find a row where the two criterias needing to match, then input the description that follows the vendor and invoice number. so a double match.
Important!!! to make this work at all you need to use ctrl+shift+enter when you use the formula, or it will give you a value!/!NA! error.
Getting a snapshot of your two worksheets would help tho.
If you need help setting it up just ask
Last edited:
Upvote
0
Worksheet 1 – needing to bring the information from column M in worksheet 2 if the vendor # & Invoice # match. In Worksheet 1 the vendor # is column G and the Invoice # is column H. Screen Shot Pictures, Screen Shot Images, Screen Shot Photos, Screen Shot Videos - Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting This link is the screen shot of both worksheets.
Upvote
0
Code:
=INDEX(Sheet2!M:M;MATCH(Sheet1!G1&Sheet1!H1;Sheet2!A:A&Sheet2!C:C;0))then this is your formula in the first cell of the column of your choosing.. when you copy paste this in
************
Press ctrl+shift+enter
************
if you just hit enter you will get an error.
You could do this with a Macro instead tho, might be faster then arrays if you have 4k rows as sheet1 have.
Edit: if you struggle with this last part there are youtube vids 2min long that explains it for you
Edit2: I see its column B, so insert it there
Last edited:
Upvote
0
Hmm, I just Pasted it in straight off your input there, before making a sheet called "Aging". And right after pasting it in (I select the whole area I want the formula to be in, and paste, then hit ctrl+shift+enter), before doing anything else just hit "ctrl+shift+enter". Your formula works on my end :S pasted it right in
Upvote
0
Sorry I pasted a screen shot and then it turned into that. Where can I access addons/programs to use? I have tried to even email to pay for support to get the formula to work and it said the mail box is full. Is there anyway to attach a copy of the file so maybe someone can see what is wrong. It still isn't working.
Upvote
0
Similar threads
- Question
