Google sheet query 2 columns as search key and search

user_19253209 0
Viewed 39

I met some problem with google sheet function.

I have 2 tables. I want to search table1 Date+User as key value in table2. example:

Date      User  Unit
2022/05/30  A   109
2022/05/30  B   119
2022/05/30  C   119
2022/05/29  D   109
2022/05/29  E   114

Date      User  Amount
2022/05/30  A   1
2022/05/30  B   2
2022/05/30  C   3
2022/05/30  D   41
2022/05/30  E   5
2022/05/29  D   6
2022/05/29  E   7
2022/05/29  F   81
2022/05/29  G   9
2022/05/29  A   101
2022/05/29  B   11
2022/05/29  C   121
2022/05/29  D   13

after query I hope the table looks like

Hope Result         
Date       User Unit    Amount
2022/05/30  A   109       1
2022/05/30  B   119       2
2022/05/30  C   119       3
2022/05/29  D   109       6
2022/05/29  E   114       7

This is a sample google sheet https://docs.google.com/spreadsheets/d/1oxhWMVPt-GziG10agob-xbiNYfKrZVFK9ro0Pj7tn6Y/edit#gid=0

Can I ask for help ?

Many Thanks

google-sheets-formula spreadsheet
1 Answers

Two options. The first pulls all matching combinations of DATE and USER

=ARRAYFORMULA(
  QUERY(
   {E2:G,
    IF(ISBLANK(E2:E),,
     IFERROR(
      VLOOKUP(
       E2:E&"|"&F2:F,
       {A2:A&"|"&B2:B,C2:C},
       2,FALSE)))},
   "select Col1, Col2, Col4, Col3
    where Col4 is not null
    label
     Col1 'Date',
     Col2 'User',
     Col3 'Amount',
     Col4 'Unit'"))

which returns

Date User Unit Amount
2022/05/30 A 109 1
2022/05/30 B 119 2
2022/05/30 C 119 3
2022/05/29 D 109 6
2022/05/29 E 114 7
2022/05/29 D 109 13

The second matches your output exactly, but does omit that second D value for the 29th (13)

=ARRAYFORMULA(
  QUERY(
   {IFERROR(
     VLOOKUP(
      UNIQUE(E2:E&"|"&F2:F),
      {E2:E&"|"&F2:F,E2:G},
      {2,3,4},FALSE)),
    IFERROR(
     VLOOKUP(
      UNIQUE(E2:E&"|"&F2:F),
      {A2:A&"|"&B2:B,C2:C},
      2,FALSE))},
   "where Col4 is not null
    format Col1 'yyyy/mm/dd'"))

Both have been added to your sheet. If either of these work out for you, I can break it down.

user_122540980
Related