CODEWITHSUNDEEP
regexgoogle-sheetsarray-formulasgoogle-sheets-formulags-vlookup
Ralph Schipper
Ralph Schipper

Reputation: 741

Converting to arrayformula with vlookup goes wrong

I have a working formula here:

=IF(ISTEXT($D3);
IF(VLOOKUP($D3;klanten;15;FALSE)>15;
REGEXREPLACE(JOIN(" ";ARRAYFORMULA(VLOOKUP($D3;klanten;
{6\5\3\4};FALSE)));"\s{2,}";" ");
REGEXREPLACE(JOIN(" ";
ARRAYFORMULA(VLOOKUP($D3;klanten;{2\4};FALSE)));"\s{2,}";" "));"")

I copy and paste it down and that works.

But I want to wrap an arrayformula around it, so I did this:

=ArrayFormula(IF(ISTEXT($D3:D);
IF(VLOOKUP($D3:D;klanten;15;FALSE)>15;
REGEXREPLACE(JOIN(" ";VLOOKUP($D3:D;klanten;
{6\5\3\4};FALSE));"\s{2,}";" ");
REGEXREPLACE(JOIN(" ";
VLOOKUP($D3:D;klanten;{2\4};FALSE));"\s{2,}";" "));""))

Now my result is #N/A and the error is (when I point at the cell)

The value '' was not found in the VLOOKUP evaluation.

What am I missing here?

EDIT: here is a copy and stripped version of my sheet: Copy

Upvotes: 0

Views: 55

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(ISTEXT(D2:D);
 IFERROR(IF(IFERROR(VLOOKUP(D2:D; klanten; 15; 0))>15;
 IFERROR(VLOOKUP(D2:D; klanten; {6\ 5\ 3\ 4}; 0));
 IFERROR(VLOOKUP(D2:D; klanten; {2\ 4}; 0)))); )); ; 999^99))))

enter image description here

Upvotes: 1

Related Questions