Combining hlookup and vlookup

1
votes

I am trying to import some data from one sheet to another using lookups.

I have one sheet with some data including date, name, and type of data(Puschases, Shops). https://docs.google.com/spreadsheets/d/1sg3fh3giXlEg1SePkwZhO_RTUEPN8WBsmAJLWSKbpkA/edit#gid=0

I then have another sheet for each team member. I want the purchases and shops data(from sample data sheet) to import to their sheet by name, date, and type of data. https://docs.google.com/spreadsheets/d/1of3wCGMZ_JEZpcxvo98Riluc5OJROMLfUN9S9LIkicE/edit#gid=0

Any thoughts on how to do this? Any help would be appreaciated.

1
google-sheetsgoogle-sheets-formulagoogle-sheets-querygs-vlookupimportrange

1 Answers

1
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, QUERY(
 IMPORTRANGE("1sg3fh3giXlEg1SePkwZhO_RTUEPN8WBsmAJLWSKbpkA", "Sheet1!B1:I"),
 "select Col8,max(Col3) group by Col8 pivot Col1", 1), {3, 4}, 0)))

enter image description here

UPDATE:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, QUERY(
 IMPORTRANGE("1sg3fh3giXlEg1SePkwZhO_RTUEPN8WBsmAJLWSKbpkA", "Sheet1!B1:I"),
 "select Col8,max(Col"&MATCH(A2, 
 IMPORTRANGE("1sg3fh3giXlEg1SePkwZhO_RTUEPN8WBsmAJLWSKbpkA", "Sheet1!B1:I1"), 0)&") 
  group by Col8 pivot Col1", 1), {3, 4}, 0)))