Padded Mouse
Expert Member
Hi I am hoping that any excel gurus out there can help me because a guru I am not.
I did advanced excel training a few years ago but never really utilized or practiced the skills taught in my then employment. I have been unemployed for a few months and have just started a new job last week so really want to make a good impression on my boss.
He asked me yesterday to cross reference information from two spreadsheets and suggested I use Vlookup ( I nearly fainted!!) I have extracted the information I need from each spread sheet and have put them into spreadsheet on different sheets/pages.
There are two sheets
The first sheet is called open orders and the second is called database
The open orders sheet has two columns with 1200 rows of information. The first column is titled "PO Number" and the second column is titled "Ordered Quantity"
The database sheet has two columns with 1051 rows of information. The first column is titled "PO Number" and the second column is titled "Total (£)
As you can see there are far more purchase order numbers on the open order sheet than there are on the database sheet.
I have to firstly cross reference to find matching PO numbers between the two sheets
I also have to match the amounts on both sheets and calculate the differences/variance between the two amounts.
I am proud to say that I did manage to do a Vlookup which matched the PO numbers, but cannot seem to do this with the amounts and do not know why or what I am doing wrong. Research that I did on google today seemed to suggest that it might better to index/match than Vlookup but I am at a loss as how to do so.
I am really hoping that someone can help me out of my dilemma here please?
I did advanced excel training a few years ago but never really utilized or practiced the skills taught in my then employment. I have been unemployed for a few months and have just started a new job last week so really want to make a good impression on my boss.
He asked me yesterday to cross reference information from two spreadsheets and suggested I use Vlookup ( I nearly fainted!!) I have extracted the information I need from each spread sheet and have put them into spreadsheet on different sheets/pages.
There are two sheets
The first sheet is called open orders and the second is called database
The open orders sheet has two columns with 1200 rows of information. The first column is titled "PO Number" and the second column is titled "Ordered Quantity"
The database sheet has two columns with 1051 rows of information. The first column is titled "PO Number" and the second column is titled "Total (£)
As you can see there are far more purchase order numbers on the open order sheet than there are on the database sheet.
I have to firstly cross reference to find matching PO numbers between the two sheets
I also have to match the amounts on both sheets and calculate the differences/variance between the two amounts.
I am proud to say that I did manage to do a Vlookup which matched the PO numbers, but cannot seem to do this with the amounts and do not know why or what I am doing wrong. Research that I did on google today seemed to suggest that it might better to index/match than Vlookup but I am at a loss as how to do so.
I am really hoping that someone can help me out of my dilemma here please?
