LinkBack URL
About LinkBacks
I need to find out the price for each part number from Sheet2, and place it on Sheet1.
Vlookup made sense, however- I have 2 values to look up. The Part Number, and Loc.
I need to find out the price for each part number from Sheet2, and place it on Sheet1.
Vlookup made sense, however- I have 2 values to look up. The Part Number, and Loc.
If each combination of values will create a unique set, you can use SumProduct
Formula:
Please Login or Register to view this content.
Just plop that formula into C2 of Sheet1 as is, and copy downwards. Worked fine for me.
Last edited by daffodil11; 08-05-2013 at 04:36 PM. Reason: absolute references
In C1 enter =SUMPRODUCT(--(Sheet2!$A$2:$A$8053=Sheet1!A2),--(Sheet2!$B$2:$B$8053=Sheet1!B2),--(Sheet2!$C$2:$C$8053)) then drag it down
Array formula confirm COntrl+Shift+Enter
=IFERROR(INDEX(Sheet2!$C$2:$C$8053,MATCH(A2&B2,Sheet2!$A$2:$A$8053&Sheet2!$B$2:$B$8053,0)),"")
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Guys, I tried the sumproduct and works perfectly (it does exactly what I need it to do), however it SLOWS DOWN EXCEL tremendously. Approximately 20-30 seconds for ONE calculation.
Take in mind that in reality, I have 13 worksheets (for 13 months) with raw data (part number, location, quantity and others)
EAch one is approximately 46k tall, and about 20-30 wide.
I then have another worksheet which summarizes these 13 sheets, it has basic columns, like part number, location, vendor id, buyer and quantity.
I do have 13 months, so I am doing a sumproduct every other column for each month, all the way to 46k cells down.
If i even double click the cell (for auto-calculation), the excel program will freeze.
I've tried it in excel 2007, 2010, 2013 and in two different computers (one running 4.0ghz i7 quad-core, and 16gb of ddr3).
It happens on all of them- I have also turned off auto-calculation to MANUAL, however, when it is time to "calculate" IT just freezes. I would have to be doing batches of 10-15 cells down (taking approx. 1-3 min each), which obviously is not going to work.
ANY IDEAS or any other formula??
It took about 15 second with my array formula.
Other suggestion could only be VBA
Try with a helper column to eliminate the array.
In sheet2 D2, copied down...
=A2&B2
then in sheet1 use this, copied down...
=INDEX(Sheet2!$C$2:$C$8053,MATCH(Sheet1!A2&Sheet1!B2,Sheet2!$D$2:$D$8053,0),1)
To cater for the 13 worksheets, click on the 1st mth tab, hold SHIFT and click on the last month tab (this will GROUP them), then copy that helper down on the 1st sheet - it will be copied down on all sheets. Then click on any other sheet outside of that "group" to ungroup them
alternatively (and this may even be better) copy ALL data onto 1 sheet (add a month column if needed), and then base the summary on that sheet.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Robert,
Thank you for your help. I haven't tried your way yet.
What is formula confirm? Do I press, Ctrl+Shift+Enter, to make it a "array formula"?
I must admit that on the test sheet, yours worked 50%. I am just confused by your formula.
...(INDEX("array" - is where the values im looking for are located
...,MATCH(A2&B2 are the values im looking for,"array to look for the values A2/B2"
Can i use more values after match? Ill just have to expand my array correct? Is there any particular rules for this formula? What if my values are in column H, V and Z ?
Thank you
Also, Can you walk me through the index/match?
Holy MACROS!
haha, your formual is yet the FASTEST ONE OF THEM ALL (take in mind that i am not at work, so just playing with that test file).
It does involve an extra step however. So i will try the shift method, to group all the month worksheets, and then do the A2&B2 on the first worksheet so it copies down on ALL worksheets.
If merge them into one worksheet, I will have to create a month column, which would probably complicate things for the summary worksheet (4 values to look for instead of 3; part/location/price/month) - plus then I will have to transpose.
Can you further explain your index formula?
Thank you so much guys, this website is soo much useful than mrexcel....
Sure
there are actually 3 functions in 1 there...Index/match/match
=index(range,row,column)
I kept it simple and just used column C as the index range.
to find the row, I used the MATCH() function
=Match(Criteria,range,exact/lower/higher)
=match(...find...A1&A2...this combines the 2 criteria, to match the helper...in the range D2:D8053,0...0=exact match)
Then for the column, I just ised 1, because the index range has only 1 column.
=INDEX(Sheet2!$C$2:$C$8053,MATCH(Sheet1!A2&Sheet1!B2,Sheet2!$D$2:$D$8053,0),1)
This can be expanded on, so that you can pick which column you want to look in, too. Instead of 1 at the end, you could use MATCH() to find a specific column to search in, just like we did with the MATCH() tro find the row to search in
Hope that helps?
1) Yes please confirm with Control+Shift+EnterOriginally Posted by cesarmontoya
2) Yes for INDEX/MATCH locations.
3) Yes you can use more values after MATCH and you need to expend all arrays
4) The rules are: INDEX is the column to return MATCH columns to look at- does not matter where they are on spreadsheet.
FDibbins gave you great alternative but if you can add column before first column in Sheet2 and then concatenate A2&B2 then you can use ordinary VLOOKUP to retrieve your values.
I'm not sure about efficiency but logic will say:1 function is less than 2.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks