Excel formula to check a text value from a cell against table's first column and return a value from the table second column

excel formula: Formula in column B (cell B1, B2, …) must check if a word from C1:C4 is used in A1 and return a corresponding value from D1:D4.

In result, the column B should look like below. This is an example of input data and results in the column B.

Go 2 steps M Jump J
Turn left T Go M
Jump fence J Turn T
Run forward M Run M
Turn around T
Go 5 steps M

I have done that some time ago using INDEX, MATCH AND VLOOKUP or HLOOKUP but can’t find it any-more. I remember that it took me long time to make it work. If a cell in column A is empty, corresponding cell in column B stays clear.

>Solution :

You can use the LOOKUP() function to accomplish the desired output in column B

enter image description here

• Formula used in cell B1


And Fill Down for the rest of the cells.

Leave a Reply