If column contains value (1,2,3,4,5,...) then return text

Question

I would gladly know how can I use a formula in Excel so that when in a certain column, a certain number is displayed, then this number is matched to a certain text that is displayed in a Excel row?

For instance:

if cell A1 = 1; then I want the value: "dinner room"

if cell A2 = 2: then I want the value: "bathroom"

if cell A3 = 3: then I want the value: "hall"

etc...

So in short, I would like to have a certain number to return a certain exact text; 1 means "dinner room", 2 means "bathroom", 3 means 'hall"

Thank you in advance for sharing your thoughts!

Greetings

Answer 1

Hi Alex,

Thank you for reaching out to us on the Microsoft Q&A forum.

With Excel, you may accomplish this by use the VLOOKUP or IF functions. Here's how to use both approaches to accomplish it:

Using IF Function:

You can nest multiple IF functions to match numbers to specific text. For example, if you want to display the text in column B based on the number in column A, you can use the following formula in cell B1:

=IF(A1=1, "dinner room", IF(A1=2, "bathroom", IF(A1=3, "hall", "other")))

This formula checks the value in cell A1 and returns the corresponding text. If the value doesn’t match any of the specified numbers, it returns “other”.

Using VLOOKUP Function:

A more scalable approach is to use the VLOOKUP function with a lookup table. First, create a lookup table with the numbers and their corresponding text. For example, you can place the table in cells D1:E3:

D1: 1 E1: dinner room

D2: 2 E2: bathroom

D3: 3 E3: hall

Then, use the following formula in cell B1 to look up the text based on the number in cell A1:

=VLOOKUP(A1, $D$1:$E$3, 2, FALSE)

This formula searches for the value in cell A1 within the range D1:E3 and returns the corresponding text from the second column of the range.

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

Answer 2

Hi @Alex

If there are many numbers, you may create 2 columns that include the certain number and the corresponding text.

Like the following image:

Then use the formula to index text, such as =LOOKUP(E2,A2:A6,B2:B6) formula.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 3

Try this formula:

 =CHOOSE(A1,"dinner room","bathroom","hall")

To show "Unknown" if the number is incorrect:

 =IFERROR(CHOOSE(A1,"dinner room","bathroom","hall"),"Unknown")

In some languages the “,” must be replaced with “;”.