CODEWITHSUNDEEP
excelif-statement
user2428927
user2428927

Reputation: 1

IF statement that inserts text based on matching text

I have a large table in excel that has column headings A, B, C, D, and ANSWER

A, B, C, D represent the multiple choice questions I have in the table.

I also have an ANSWER column that has the answers represented by the corresponding multiple choice heading letter mentioned above.

For example, under each column heading (A, B, C, D), I have the possible answers, Food, Car, House, School.

In the example above, House (C) is the correct answer. I would like to create an IF statement that matches the Answer cell, which has C in it with the A, B, C, D column headings and if there's a match, then insert an = before the actual answer. In this case, the result would be =house. The rest of the answers should have the ~ inserted before the word, i.e., ~food, ~car, ~school.

The final result should look like this: ~food, ~car, =house, ~school.

Upvotes: 0

Views: 714

Answers (1)

Daniel Möller
Daniel Möller

Reputation: 86600

The only way to achieve that securely is to duplicate the table in another sheet or in different columns.

Why is that? Because you want to change the SOURCE of your statement. Excel would consider that a circular reference and couldn't resolve it.

There's a VBA code solution, that you can run ONLY ONCE. That would change the source data as well and you would lose your originals. Could be an action with no turning back.

So:

I suggest you create a new sheet, put the headers A, B, C and D. So you would have two sheets: the OriginalSheet containing the answers and each option. And the ResultSheet, containing the options formated as you want.

In the ResultSheet, use this formula:

= IF(OriginalSheet!$E2 = A$1; "="; "~") & OriginalSheet!A2

That is considering the first line containig the texts: A, B, C and D. So you must insert this formula in the A2 cell of the ResultSheet. You can click in the little black square in bottom right of the cell and drag this formula to all other cells. (The $ simbols garantee the drag will be safe)

Upvotes: 2

Related Questions