CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulavlookupflattentextjoin
JamesReed68
JamesReed68

Reputation: 419

VLOOKUP & TEXTJOIN - Return data from 2 columns, and join them into a single column

How can I use VLOOKUP, and return 2 columns of data joined into 1 column?

Here is a current working formula that will return the first & last name in a different column.

=VLOOKUP(Sheet2!A2:A,Sheet1!A2:C,{2,3},FALSE)

Here is a non-working formula How do I fix this formula to make it return {2,3} in a single column?

=VLOOKUP(Sheet2!A2:A,Sheet1!A2:C,TEXTJOIN(" ", TRUE, {1,2}),FALSE)

Goal: I would like to add a formula so when the ID is entered in the Employee Lookup tool, it will return the first & last name in the same cell.

Sheet1 - Employee Database

ID FirstName LastName
x11111 John Doe
x22222 Sarah Smith

Sheet2 - Employee Lookup by ID tool

ID First&LastName
x11111 =VLOOKUP(Sheet2!A2:A,Sheet1!A2:C,TEXTJOIN(" ", TRUE, {1,2}),FALSE)
x22222 Sarah Smith

Upvotes: 1

Views: 1277

Answers (2)

Sergey
Sergey

Reputation: 1131

You can prepare your input data before you search for it in VLOOKUP()

using this conversion {Sheet1!A2:A,Sheet1!B2:B&""&Sheet1!C2:C} your Employee Database will change to

enter image description here

this is how it will look in the formula =ArrayFormula(VLOOKUP(Sheet2!A2:A,{Sheet1!A2:A,Sheet1!B2:B&" "&Sheet1!C2:C},2,FALSE))

Upvotes: 1

player0
player0

Reputation: 1

try:

=INDEX(FLATTEN(QUERY(TRANSPOSE(IFNA(
 VLOOKUP(Sheet2!A2:A, Sheet1!A2:C, {2, 3}, 0))),,9^9)))

Upvotes: 2

Related Questions