# 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.

## Files

• 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

## Files

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

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.

• 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.

• 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

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

• 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.