I'm trying to use SPLIT()
here, but it only gets the first row:
Notice that the ranges are obtained dynamically, so that moving columns around won't lose the colum reference:
=ARRAYFORMULA(
INDIRECT("Sheet1!"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+")&"5:"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+"))&"|"&
INDIRECT("Sheet2!"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")&"5:"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")))
This generates, which is not the end result:
The idea is to split it by the | and stack them up and avoid blanks.
Here's a sample of the data: https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing
Thank you!
use:
=ARRAYFORMULA(QUERY({
INDIRECT("Orders!"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+")&"6:"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+"));
INDIRECT("Scenarios!"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+")&"6:"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+"))}, "where Col1 is not null", ))
=INDEX(QUERY({
INDIRECT( "Orders!"&ADDRESS(6, MATCH("PO #", Orders!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #", Orders!5:5, ), 4), 2, ));
INDIRECT("Scenarios!"&ADDRESS(6, MATCH("PO #", Scenarios!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #", Scenarios!5:5, ), 4), 2, ))},
"where Col1 is not null", ))