How to return 2 columns using VLOOKUP

[GSmith8]

Hi all,

I have a book with 2 sheets

Claim Submission - Decemner & Claim Submission - January

In both sheets column I is an Invoice ID number, they are all unique

I need to look up this column on both sheets and if there are any that match, i need to return the data from columns V & W in the december sheet

it doesnt matter where i return this info to, it can be a new sheet if that is easier (i added a sheet called Duplicates)

How do i run the lookup and return these 2 columns, should the Data in Column I match on both sheets?

Thanks in advance

[azumi]

Describe it the problem and the wanted result in small sample workbook, and upload it in the forum

[GSmith8]

Example Attached.

Invoice ID is in column I on both sheets

i need to look up these 2 columns. If there is a duplicate in column I between the 2 sheets i need to return the data in columns V&W

It doesnt matter where this is returned to. It can go on the sheet Duplicates?

Thanks

[benishiryo]

hi GSmith. do take a minute to read about cross-posting. while it's advised not to do that, it's fine if you at least place a link where you cross-posted.
http://answers.microsoft.com/en-us/o...f-c6bf7c7fe0a5

try this array formula in X2 of Duplicates sheet:

Please Login or Register  to view this content.

....confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site)

[GSmith8]

Have used the following:

In Duplicate Sheet
A1
='Claim Submission - January'!$I2
fill it down to pick up all the invoice IDs from the January sheet, then in column B put this formula:

=COUNTIF('Claim Submission - December'!I:I,A2)

In column C, row 2:
To have V and W in single cell:

=IF(B2>0,VLOOKUP(A2,'Claim Submission - December'!I:W,14,FALSE) & " | " & VLOOKUP(A2,'Claim Submission - December'!I:W,15,FALSE) ,"")

Thanks!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.