CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulavlookuparray-formulas
user14915635
user14915635

Reputation: 396

Vlookup from One Column to Place Values in Multiple Columns

I'm trying to use vlookup to return multiple phone extensions for the same room number. Normally, I would be able to accomplish this using vlookup, but it only returns one value from one column. Is there a way I can get vlookup to return multiple values within same column across multiple cells?

In the sample spreadsheet I've provided that you can edit, I have the same room number multiple times in column A within tab titled "Phones" because sometimes there is more than 1 phone in the same room. I'm wanting to use an arrayformula that uses vlookup in tab titled "Users" to bring over each phone for the same room, in columns B and C, titled Phone 1 and Phone 2.

Thanks for your help!

Upvotes: 2

Views: 185

Answers (3)

kishkin
kishkin

Reputation: 5325

Here's a solution to get the rooms alongside with phone numbers all at once (a formula in a single cell):

=ARRAYFORMULA(
  TRIM(
    SPLIT(
      TRANSPOSE(QUERY(
        QUERY(
          FILTER(Phones!A:B & "♥", Phones!A:A <> ""),
          "SELECT MAX(Col2)
           GROUP BY Col2
           PIVOT Col1",
          1
        ),, COUNTA(Phones!B:B)
      )),
      "♥"
    )
  )
)

enter image description here

And if you really need to search the room number in a reference (e.g. if column A:A has some subset of room numbers) then here's a solution for a single cell where the formula will fill everything down:

=ARRAYFORMULA(
  IF(
    A2:A = "",,
      TRIM(
        SPLIT(
          VLOOKUP(
            A2:A,
            SPLIT(
              TRANSPOSE(QUERY(
                QUERY(
                  FILTER({Phones!A:A & "♥", Phones!B:B & "♦"}, Phones!A:A <> ""),
                  "SELECT MAX(Col2)
                   GROUP BY Col2
                   PIVOT Col1",
                  1
                ),, COUNTA(Phones!B:B)
              )),
              "♥"
            ),
            2,
          ),
          "♦"
        )
      )
  )
)

enter image description here

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36965

You can try FILTER() function.

=TRANSPOSE(FILTER(Phones!B:B,Phones!A:A=A2))

enter image description here

Upvotes: 2

flesherdan
flesherdan

Reputation: 1

Ahoy - it isn't super elegant, but you can do a countif, where your range starts at the top cell of the room number, and the bottom part of the range moves down with each cell. This way, you're essentially saying "for this specific room number, which number in the list is the specific row I'm in). Then, you can concatenate the room number and the occurrence (e.g. room 405, first occurrence would be 4051), and then do an index match in the two columns for the extension numbers, where you add whether you want the first or second occurrence to the lookup value of the match formula. If you aren't familiar with index match, there are lots of good youtube videos explaining it, works very similarly to vlookup. I put the formulas in your sheet, hope that helps!

Upvotes: 0

Related Questions