Log In
Register
Home
Active Member
July 31, 2015
Offline
Hello all,
My brain hurts and has turned to mush. My VP has given me 2 spreadsheets of item numbers and projected volume through 2018. I have summarized 1 with a pivot table. My issue is she would like me to take my pivot table and bring in 2 columns from the un-summarized spreadsheet using item # as the reference. These do not need to be in the pivot table they can easily be next to it. So, I need to look at the item number in the pivot table and compare it to the item number in the second spreadsheet and return the value listed in the second spread sheet to the first. Clear as mud right???? I for the life of me can not get it to work. I wish I could upload it but its sensitive company info and they'd chuck me to the curb for it lol. I have tried a VLook up and reading that maybe I need to do and index. I'm just not sure the best course of action for this.
Please help or ask more questions if needed. Thank you!!!!!!
VIP
Trusted Members
June 25, 2016
Offline
Hi Rachel
Both VLOOKUP and INDEX/MATCH can be use depending on the setup of the data on your 2nd spreadsheet.
VLOOKUP can only find values to the right of the lookup value (in your case the ITEM #) while using INDEX/MATCH it can lookup any column.
My attachment contains sample using both VLOOKUP and INDEX/MATCH.
Hope this helps.
Sunny
VIP
April 21, 2015
Offline
Nice one Sunny. I would have thought about adding an extra column to the data and from there show it in the Pivot Table.
But your solution sticks better to what is descriped in the question and I didn't know this works also. You maybe have a problem with more complex pivot tables where the first column repeats (without showing the value or item) because there are more items in the second and third column before you have a value in the value area. I think my 'solution' works there as well.
VIP
Trusted Members
June 25, 2016
Offline
Hi Frans
Without seeing the data, I can only make a guess. There can be many ways to handle the situation and your's may also work.
Since she mentioned Item # as the reference, I can only assume it is in one column and no duplicates (especially if there are more columns after the item #).
Otherwise we may need to concatenate the required columns on both sheets for the VLOOKUP or INDEX/MATCH to work.
Sunny
1 Guest(s)
