How can I use VLOOKUP, and return 2 columns of data joined into 1 column?
- Solution must be ARRAYFORMULA compatible.
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 |
CodePudding user response：
try:
=INDEX(FLATTEN(QUERY(TRANSPOSE(IFNA(
VLOOKUP(Sheet2!A2:A, Sheet1!A2:C, {2, 3}, 0))),,9^9)))