问题描述:

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.

Thanks

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 `stream`

and `site`

.

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.`(2) The`

INDIRECT`function treats a`

String`as a`

Range`. We are passing in Cells`

A2`and`

B2`, which are "Midstream" and "New York", respectively. We use the`

SUBSTITUTE`function to remove the spaces since they aren't allowed. So now, we are looking up a named range called`

MidstreamNewYork` (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.

您可能感兴趣的文章：

- angular2 - Hash in Spring Security url
- excel - Data not getting saved after copying data from one sheet to another using java
- Persistent values in ListBox in Excel VBA
- javascript - How to hide multiple axes plotly?
- curl - How to get the result of a command in a HERE_doc in Bash
- php - Reading a specific line from a text file
- clr - Any implementation of an Unrolled Linked List in C#?
- Finding Hudson Log Files
- Forward to a payment-gateway together with POST data using cURL (or any other PHP server side solution)
- WCF in Winforms app - is it always single-threaded?

随机阅读：

**推荐内容**-

**热点内容**