Excel VLOOKUP function cannot return newline characters

Question

Please look at this gif.

The function call is =VLOOKUP(A1, A1:B1, 2, FALSE).

I want:

1. b
2. c

but got:

1. b2. c

the newline character loosed.

Answer 1

You have used the correct formula. However, excel just shows that in one line. click on the cell and do "wrap text". Then it will show them in two lines.Check this image for reference

Also check this link where there is a similar issue: https://www.excelforum.com/excel-formulas-and-functions/649990-vlookup-preserve-carriage-return-alt-enter.html