0

I have a long table with three columns, looking like this:

+-----+-------+-------+
| URL | Label | Value |
+-----+-------+-------+
| u1  | l3    | v1    |
+-----+-------+-------+
| u3  | l4    | v3    |
+-----+-------+-------+
| u2  | l2    | v2    |
+-----+-------+-------+
| u4  | l1    | v4    |
+-----+-------+-------+
| u3  | l5    | v3    |
+-----+-------+-------+
| u1  | l2    | v1    |
+-----+-------+-------+
| u4  | l3    | v4    |
+-----+-------+-------+
| u2  | l4    | v2    |
+-----+-------+-------+

I want to quasi pivot it, to let it look like:

+-----+-----+-----+-----+-----+-----+
| URL | l1  | l2  | l3  | l4  | l5  |
+-----+-----+-----+-----+-----+-----+
| u1  | #NV | v4  | v1  | #NV | #NV |
+-----+-----+-----+-----+-----+-----+
| u2  | #NV | v2  | #NV | v2  | #NV |
+-----+-----+-----+-----+-----+-----+
| u3  | #NV | #NV | #NV | v3  | v3  |
+-----+-----+-----+-----+-----+-----+
| u4  | v4  | #NV | v4  | #NV | #NV |
+-----+-----+-----+-----+-----+-----+

To do so I've written a VLOOKUP formula with two searching criteria, looking like this:

=VLOOKUP($E2&F$1,CHOOSE({1.2},$A$2:$A$9&$B$2:$B$9,$C$2:$C$9),2,0)

entered with CTRL+SHIFT+ENTER.

I'm not sure, whether I correctly translated the formula from German into English - my original working formula for german Excel looks like:

=SVERWEIS($E2&F$1;WAHL({1.2};$A$2:$A$9&$B$2:$B$9;$C$2:$C$9);2;0)

The formula works like a charm in this example.

My problem: original table I need to process is around 700.000 rows long - after Excel calculated it the whole last night, the calculation wasn't even ready.

What could be a way to calculate more efficiently?

Evgeniy
  • 2,337
  • 2
  • 28
  • 68
  • 2
    Look into INDEX MATCH no need for array. – P.b Jun 17 '21 at 14:31
  • You can try to play with XLOOKUP function. Please check great article about it: [XLOOKUP just killed VLOOKUP](https://www.powerusersoftwares.com/post/xlookup-just-killed-vlookup-everything-to-know-about-this-major-new-excel-function) – Szymon Jun 22 '21 at 12:16

3 Answers3

1

One simpler alternative without using intensive array formula would be to use a helper column

In column D, to produce concatenation:

=A2&B2

Once this is done then it is fairly straight formula in cell F2:

=IFERROR(INDEX($C:$C,MATCH($E2&F$1,$D:$D,0)),"#NV")

Copy down and across.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
0

This is the formula for F2 in the appended image, copied from there to F2:J5.

=IFERROR(INDIRECT(ADDRESS(SUMPRODUCT((INDEX(Data,0,1)=$E2)*(INDEX(Data,0,2)=F$1)*ROW(Data)),3)),"-")

enter image description here

The formula in F1 is shown next. The formula in E2 is similar and shown in the image.

=TRANSPOSE(UNIQUE(INDEX(Data,0,2)))

And this is the formula defining the named range Data:-

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1, 3)

Both the first and last formulas are volatile and that will make a worksheet with 700,000 rows sluggish. For the OFFSET function that doesn't matter because it's only two of them. For the INDIRECT function, I presume that you will convert the result to values and the formulas will not stay in the workbook.

Variatus
  • 14,293
  • 2
  • 14
  • 30
0

Not quite sure it works without array though (I have office365 and can't see the difference), but try this:

=INDEX($C$1:$C$9,MATCH($E2&F$1,$A$1:$A$9&$B$1:$B$9,0)) enter image description here

In German that would be: =INDEX($C$1:$C$9;VERGLEICH($E2&F$1;$A$1:$A$9&$B$1:$B$9;0))

PS this formula is non-volatile and doesn't need helpers.

You could also wrap it in IFERROR, which will only show the matches and shows blank if there's no match: =IFERROR(INDEX($C$1:$C$9,MATCH($E2&F$1,$A$1:$A$9&$B$1:$B$9,0)),"")

P.b
  • 8,293
  • 2
  • 10
  • 25