Lookup column value and find values in corresponding row

  • Hi,


    My request is to help me find a formula (or VBA code) to show my three weeks with the largest sales customer A, B or C.
    I found something similar here
    Lookup column value and find nth values in corresponding row
    but the requirements are different.


    See attach.
    Thanks advance.

  • Re: Lookup column value and find values in corresponding row


    Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in B8. Hopefully, I understood you correctly.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Lookup column value and find values in corresponding row


    Mumps,


    Thanks for the code but in C3:Q5 data are entered by centralizing data from other sheets. I need to be able to view the C8:C12 week number that corresponds to the top 3 sales (or more) of D8:D12. It all depends on what you choose in B8.


    example:
    If B8 is the "C" in D8: D12 will be top 3 sales related range C5:Q5
    If B8 is "A" in D8: D12 will be top 3 sales related to range C3:Q3


    What is displayed in C8: D9 is how I want to be (with formulas or VBA code).
    Obviously if the B8 is - let's say "A" - I have other values in C8: D9.


    Sorry I did not explain clearly.

  • Re: Lookup column value and find values in corresponding row


    Hi Ingo_Ingo,


    You can do what you're after with formulas. In order to achieve this, I have taken a couple of liberties:


    1. In cells E8:E11, I have added the incremental ranking numbers 1-4, i.e. 1 in E8, 2 in E9, and so on.
    2. In order to create unique numbers (that you don't see in the results), each number in your table is reduced by its column number divided by 100000. This gives us the ability to separately identify the otherwise same numbers in E3 and N3. In the results for search type A, week 3 would be listed ahead of week 12.


    In cell C8, enter the formula:
    =INDEX($C$2:$Q$2,1,MATCH(LARGE(OFFSET($B$2,MATCH($B$8,$B$3:$B$5,0),1,1,COUNTA($C$2:$Q$2))-(COLUMN($C$2:$Q$2)/100000),$E8),OFFSET($B$2,MATCH($B$8,$B$3:$B$5,0),1,1,COUNTA($C$2:$Q$2))-(COLUMN($C$2:$Q$2)/100000),0))
    This is an array formula, and must be confirmed with SHIFT + CTRL + ENTER instead of just ENTER.


    In cell D8, enter the formula:
    =OFFSET($B$2,MATCH($B$8,$B$3:$B$5,0),$C8)
    This is a standard formula that you should confirm with just ENTER.


    You can then drag down the formulas to the remaining rows.


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]