Need a formula to pull values into column A based on the corresponding values in B and C. Actual data is in column F through H.
In column C, large function was used to extract the largest to the smallest values - based on values in column H. Corresponding region values were pulled. Need a formula to pull the corresponding product list in A - desired outcome is highlighted in yellow in A.
Sample workbook attached.
If I understand you correctly, try this array formula, copied down...
=INDEX($F$2:$F$27,MATCH(B2&C2,$G$2:$G$27&$H$2:$H$27,0)) CSE
If "reagion" and Arrival Time" are rghe same though, it will bring back the 1st instance of the match
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
FDibbins: Proposed array formula is returning the same pulled values for the 1st 2 instances. See below:
PlayStation 3 Southern 100
PlayStation 3 Southern 100
Instead of
PlayStation 3 Southern 100
DVD Player Southern 100
Thanks for the help.
Yes, perhaps you mised this part?
Add a helper (I used I) and copy this down...If "region" and Arrival Time" are the same though, it will bring back the 1st instance of the match
=G2&H2&COUNTIFS($G$2:G2,G2,$H$2:H2,H2)
Then use this regular formula, copied down...
=INDEX($F$2:$F$27,MATCH(B2&C2&COUNTIFS($B$2:B2,B2,$C$2:C2,C2),$I$2:$I$27,0))
FDibbins: Is there a way to have the COUNTIFS modified to COUNTIF OR any other function in EXCEL 2003 to achieve the same result.
Last edited by bjnockle; 11-02-2013 at 08:40 PM.
FDibbins: I was able to get around the COUNTIFS with SUMPRODUCT: =G2&H2&SUMPRODUCT(--($G$2:G2=G2),--($H$2:H2=H2))
=INDEX($F$2:$F$27,MATCH(B2&C2&SUMPRODUCT(--($B$2:B2=B2),--($C$2:C2=C2)),$I$2:$I$27,0))
The suggested helper column, COUNTIFS formula did the trick!
Thanks a million, FDibbins!
Last edited by bjnockle; 11-02-2013 at 09:01 PM.
Happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks