Megring data in Excel

I really should know how to do this, I suspect it is quite simple.
I have two Worksheets, they both contain one column which has an ID, the ID identifies the same products in both sheets. One sheet has one sellers price for that product and the other sheet has another sellers price for that product, what I want to do it show on either sheet what the two different prices are for that ID.

So "IF the ID =  100 in sheet 1 return the price of ID = 100 from sheet 2 to column X in sheet 1"

Does that make any sense at all?
LVL 1
edwaltersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
Can you merge data by id using a vlookup
edwaltersAuthor Commented:
I did look at doing that, but couldn't get it to work, would you have to sort the data into order first for vlookup to work?
Leo TorresSQL DeveloperCommented:
Not really but its better for preformance
=vlookup(sourceid,externalid:range,columntoreturn, false)

The ids must be the first column when selecting a range
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Tina KSystems SpecialistCommented:
=index('sheet2'!b2:b50, match('sheet1'!a1, 'sheet2'!a1:a50))

So, first argument is the range of values on sheet 2 that you'd find the pricing in.  (sheet2, range)
Second argument is the customer id you're looking for on sheet 1 (sheet1, range a1)
third argument is where you are looking for the matching value of customer id from sheet 1 (sheet2, range)

Put this in your first cell of column x on sheet 1 (making sure cell refs are correct) and copy down
Tina KSystems SpecialistCommented:
Sorry about that - not customer id...product id - didn't mean to confuse!
Leo TorresSQL DeveloperCommented:
edwalters did you need to match on multiple columns?
Saurabh Singh TeotiaCommented:
Here is working example of the file what leo posted is you can do the same by vlookup...

Please refer to workbook for details...

Saurabh...
Vlookup.xlsx
edwaltersAuthor Commented:
Many thanks for all your help, i'll see what I can do this evening and let you know how I get on.  Again many thanks for such a quick response.
edwaltersAuthor Commented:
I'm just not very good at this.  This is what I have in B4 of my sheet.   LiveData is the sheet I want to extract data from.  A2:I247 is the Table in LiveData.

=VLOOKUP(A4,'Live data'!A2:I247,3,FALSE)

What I'm trying to do is return the name of a product which appears in 'Live Data'  Column B is that the 1st or 2nd column when the ID is in column 1?

Column A in both sheets contain the ID which I'm using for the lookup
Saurabh Singh TeotiaCommented:
Change this formula to..

=VLOOKUP(A4,'Live data'!A2:I247,3,FALSE)

to this..

=VLOOKUP(A4,'Live data'!A2:I247,2,FALSE)

The 3 parameter is the table value which you want to pick up in vlookup starting with the first column of your table...

Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.