I need to reduce a file I am developing and I know one good way is using VBA. Unfortunately I am not advanced in VBA yet and I am struggling in designing this.
I have a list price organized in three different streams, and I want to combine in one like below:
Stream Site Brand Code Price
Mainstream Boston Brand 01 Formula
Midstream New York Brand 02
Midstream Los Angeles Brand 02
Currently I am using a formula that basically does the following:
=IF(AND(stream="mainstream",Site = "Boston"),vlookup(Brandcode,list 1, 2,0),IF(ANd(stream="midstream", Site = "Boston", vlookup(Brandcode,list 2, 2,0),...))
The formula actually works just fine, the problem is I am testing many other conditions than just this one and thus the file is becoming very heavy, so I wanted to create a VBA code to create either a function or a subroutine, but I am struggling to understand how to do it.
This can actually be achieved without using VBA by using named ranges instead. Looking at the formula, it's clear that your lookup range varies based on the combination of
You can create a
Named Range for each of these lookup ranges. To do so, highlight the range of cells that contains the first lookup group (let's say Midstream New York). Next, press CTRL + F3 to open up the
Name Manager. Finally, give this group the name
MidstreamNewYork. (Note: you cannot include spaces in the name).
Next, you can update your
Vlookup function. You no longer have to include the
IF(AND... component because your lookups will be dynamic. Let's say you're inputting a formula on Row 2, the formula would be:
=VLOOKUP(C2,INDIRECT(SUBSTITUTE(A2&B2, " ", "")),2,FALSE)
Let's break down the formula. (1) C2 is just the brandcode (I assumed it was in column C.
function treats aString
. We are passing in CellsA2
, which are "Midstream" and "New York", respectively. We use theSUBSTITUTE
function to remove the spaces since they aren't allowed. So now, we are looking up a named range calledMidstreamNewYork` (sound familiar?). (3) The rest of the VLOOKUP is standard: lookup the second column, and only match exacts.
Give it a try and let me know if it meets your requirements.