Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges
.
I am not too convinced that this is such a good example as I cannot see any advantages. But possibly I am wrong. If it does have, for example, speed advantages over a normal formula then some of my theories are probably out of wach.
But as this is often given as an example, I will demo it. Any comments or any better examples of using a formula such that it uses Implicit Intersection for single "breadth" ranges would be very welcome.
I will not go into great detail of the basic VLookUp formula and typical forms in this post. ( I have done that in the next post for revision and reference ) . In this post I will start with three columns holding 3 typical versions of the formula working on the same Look Up values and Look Up Tables, and then add a forth, the often given Implicit Intersection version.
So in the Window below are the first of a 7 row VLookUp formula in 3 versions in columns B C and D. Column E will then be considered for the Implicit Intersection version.
Code:
First row formula
| Row\Col |
B |
|---|
3 |
=VLOOKUP(A3,$A$16:$C$33,3,FALSE) |
| Row\Col |
C |
|---|
3 |
{=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)} |
| Row\Col |
D |
|---|
3 |
=VLOOKUP({"Chocolate-europe aroma";"Chocolate-Cookies";"Banana-Chocolate-Split";"Limette-Käsekuchen";"Erdbeere-Quark";"Erdbeere-Mix";"Jamaica Sun";"Waldbeeren"},$A$16:$C$33,3,FALSE) |
| Row\Col |
A |
B |
C |
D |
|---|
2 |
Given Look Up Value |
Simple Formula. First argument single Look Up value. Draged down |
Column range reference first argument - Type 2 CS Entry |
First argument array. Type 2 CS Entry |
3 |
Chocolate-europe aroma |
4 |
4 |
4 |
4 |
Chocolate-Cookies |
0 |
0 |
0 |
5 |
Banana-Chocolate-Split |
10 |
10 |
10 |
6 |
Limette-Käsekuchen |
16 |
16 |
16 |
7 |
Erdbeere-Quark |
8 |
8 |
8 |
8 |
Erdbeere-Mix |
0 |
0 |
0 |
9 |
Jamaica Sun |
6 |
6 |
6 |
10 |
Waldbeeren |
0 |
0 |
0 |
| Row\Col |
A |
B |
C |
|---|
14 |
LOOKUP Table |
Column 2 |
Column 3 |
15 |
Look Up ValueProduct Name |
|
|
16 |
Haselnuß-Walnuß-aromatisiert |
|
|
17 |
Tiramisu |
|
2 |
18 |
Chocolate-colonial blend |
|
|
19 |
Chocolate-europe aroma |
|
4 |
20 |
Chocolate-Cookies |
|
|
21 |
Jamaica Sun |
|
6 |
22 |
Himbeere-Joghurt |
|
|
23 |
Erdbeere-Quark |
|
8 |
24 |
Erdbeere-Mix |
|
|
25 |
Banana-Chocolate-Split |
|
10 |
26 |
Waldbeeren |
|
|
27 |
Kirsche |
|
12 |
28 |
Kirsche-grüner Apfel |
|
|
29 |
Kirsche-Ananas |
|
14 |
30 |
Stracciatella |
|
|
31 |
Limette-Käsekuchen |
|
16 |
32 |
grüner Apfel-Quark |
|
|
33 |
Blutorange-Quark |
|
|
_...........................
The characteristic , or as I would say the By product, of Intercept or Implicit Intersect for a single "breadth" Array that we utilise here is the returned single value in certain cells for a reference such as =A3:A10.
It might slowly become apparent that I did not randomly pick the rows to be used for the normal formulas. Considering the imaginary help matrix discussed in post 2 for the reference = A3:A10, we will have for that help matrix corresponding to having the formula in any cell ( other than cells A3 through to A10 ) the following:
Code:
1 |
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
3 |
|
Chocolate-europe aroma |
Chocolate-europe aroma |
Chocolate-europe aroma |
Chocolate-europe aroma |
Chocolate-europe aroma |
Chocolate-europe aroma |
4 |
|
Chocolate-Cookies |
Chocolate-Cookies |
Chocolate-Cookies |
Chocolate-Cookies |
Chocolate-Cookies |
Chocolate-Cookies |
5 |
|
Banana-Chocolate-Split |
Banana-Chocolate-Split |
Banana-Chocolate-Split |
Banana-Chocolate-Split |
Banana-Chocolate-Split |
Banana-Chocolate-Split |
6 |
|
Limette-Käsekuchen |
Limette-Käsekuchen |
Limette-Käsekuchen |
Limette-Käsekuchen |
Limette-Käsekuchen |
Limette-Käsekuchen |
7 |
|
Erdbeere-Quark |
Erdbeere-Quark |
Erdbeere-Quark |
Erdbeere-Quark |
Erdbeere-Quark |
Erdbeere-Quark |
8 |
|
Erdbeere-Mix |
Erdbeere-Mix |
Erdbeere-Mix |
Erdbeere-Mix |
Erdbeere-Mix |
Erdbeere-Mix |
9 |
|
Jamaica Sun |
Jamaica Sun |
Jamaica Sun |
Jamaica Sun |
Jamaica Sun |
Jamaica Sun |
10 |
|
Waldbeeren |
Waldbeeren |
Waldbeeren |
Waldbeeren |
Waldbeeren |
Waldbeeren |
11 |
|
|
|
|
|
|
|
12 |
|
|
|
|
|
|
|
13 |
|
|
|
|
|
|
|
So, the outcome of this is that a reference, such as that in the first argument of our VLookUp formula, se to =A3:A10 will return ( without CS Entry ) the Look Up value we require. So this formula
=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) or =VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
in rows 3 to 10 in any spare column, such as column E will give the same results as in the other columns.
The argument for using Implicit Intersection in such a way is that it is very quick.
But I do not see that it is quicker than the formulas in column B
May be I will.
Reply With Quote
Bookmarks