V and H Lookups On Dynamic Data

    Hi. Going to do my best to state what I'm attempting to do. I have a spreadsheet with two tabs. On the 'Template' tab I have a series of "ranges" (they are not named) for VPNs and colors. Within each VPN/color range is a range of sizes - from one to fifty-two possibilities. On the second tab ("IIF"), I have a list of all the sizes/colors/VPNs. In column L on the second tab ("Notes to Allocator") I want to look up the VPN, color and size and return the recommended order quantity which is six rows below the size. For example, on the first row of the second tab, the result should be 21 as that VPN/Color/Size combination is recommending 21 pieces. Is there even a way to do this without a macro? The number of VPN/Color combinations can vary from one to infinity. Any help would be great.

    Re: V and H Lookups On Dynamic Data


    Hello Jim,


    Attached is your workbook with a proposal ...


    You can obviously hide the two helper columns N and O ...


    Hope this will help

    Re: V and H Lookups On Dynamic Data


    Carim - I've noticed that you often provide a solution in a workbook, which is great, but it would be really helpful to me (and to others who might not want or be able to download the workbook) if you would also outline the solution in your post (giving the relevant formulae, etc.). Would you mind? :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: V and H Lookups On Dynamic Data


    Quote from AliGW;796910

    Carim - I've noticed that you often provide a solution in a workbook, which is great, but it would be really helpful to me (and to others who might not want or be able to download the workbook) if you would also outline the solution in your post (giving the relevant formulae, etc.). Would you mind? :)


    Hello Ali :smile:


    Always thought a visual example (shown in a workbook..) would be more explicit than an explanation with words ... :wink:


    But from now on ... will try to outline the solution in the post ...


    Cheers

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: V and H Lookups On Dynamic Data


    It is, but there are some people who don't want to have to open workbooks to see the answer. Thank you for considering my request! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: V and H Lookups On Dynamic Data


    Hello,


    On the second tab in column N, you can have the following array formula to determine the matching row number:

    Code
    =MATCH(A2&" "&SUBSTITUTE(B2," ",""),Template!A:A,0)


    Since your color descriptions are not written in the same way in both sheets, you do have to remove spaces with the substitute() function ...


    On the second tab in column O, you can have the following formula to determine the matching column number:

    Code
    =MATCH(D2,INDIRECT("Template!"&N2&":"&N2),0)


    On the second tab in column L, you can have the following formula to return the required amount:

    Code
    =INDIRECT("Template!"&ADDRESS(N2+6,O2,4))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: V and H Lookups On Dynamic Data


    Quote from abba92;797006

    SOLVED! Thank you so much! I don't mind helper columns at all.



    Glad you could fix your problem ...:wink:


    Thanks a lot ... for your Thanks ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    PC adaptation of Psiphon is known as Psiphon 3. You can read more about Psiphon underneath in the post. Presently let us check How to download and introduce Psiphon in our Windows 10/8.1/8/7 PC or Laptop. Psiphon makes an intermediary server condition for boundless confinement free Internet Surfing. According to the current refresh, the Psiphon programming is presently authoritatively accessible for Windows Pc and Laptop. That implies you can download guide and utilize Psiphon in your Windows machine, as well.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login