Google Sheets Double Array Lookup

1
votes

INPUT core data sheet:

INPUT core date

OUTPUT data sheet:

OUTPUT desired data

Essentially I am wanting to have the output sheet do a lookup on the input sheet, and Match column C(INPUT) where it matches in Column A(OUTPUT) of and ALSO match column A(INPUT) where it matching in D1:1(OUTPUT), and return column H(INPUT), and if there is no match for the member in D1:1, then show empty

The end result would look like this:

name                    AshHoIe        Barktooth      Bria Janson...

Aayla Secura           7*L85G8P12539  7*L85G8P12539  7*L85G8P12539

Admiral Ackbar         7*L85G8P12539  7*L85G8P12539

Ahsoka Tano            7*L85G8P12539                 7*L85G8P12539

Ahsoka Tano (Fulcrum)                 7*L85G8P12539

Amilyn Holdo           etc.

A better example in the Google sheet below.

Would love to do this with either a single formula per row or single formula for entire array list.

Editable Google spreadsheet here.

1
google-sheets

1 Answers

0
votes

paste in D2 and drag to the right:

=ARRAYFORMULA(IFERROR(VLOOKUP($A$2:$A, 
 QUERY(INPUT!$A$2:$H, "select C,H where A='"&D$1&"'"), 2, 0), ))

0