Array formula issue

Question

I am trying to turn this into an array formula with the first cell being the header.

=IFERROR(INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw/edit#gid=357252415", "Data!B:B"),MATCH(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw/edit#gid=357252415", "Data!A:A"),0),1))

I tried formatting it like this:

=ArrayFormula({"Name";IFERROR(INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw/edit#gid=357252415", "Data!B:B"),MATCH(A2:A,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw/edit#gid=357252415", "Data!A:A"),0),1))})

but it is only providing me with the first row value, the rest of the column remains blank.

thanks in advance

user_5632629 · Answer

fx is correct. make sure you run this first and connect your sheets by allowing access:

=IMPORTRANGE("1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw", "Data!B1")

when connected use:

=ARRAYFORMULA({"Name"; IFERROR(INDEX(
 IMPORTRANGE("1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw", "Data!B:B"), MATCH(A2:A,
 IMPORTRANGE("1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw", "Data!A:A"), 0)))})

or try:

={"Name"; INDEX(IFNA(VLOOKUP(A2:A, 
 IMPORTRANGE("1ht6brZFTrTpmh7Wr-M-59ulTRPatvAUEu0QBvqbrxhw", "Data!A:B"), 2, 0)))}

Array formula issue

1 Answers