If a particular word exists within a field then as

2019-01-29 06:30发布

Reference Spreadsheet >> Spreadsheet - I need a formula in column B that searches through column A for text values that exist in the legend (column E), then if text is found, assign corresponding value that exists in column F.

The alternative for this is to use a formula, but instead of this, I want to use a table that I can modify rather than modifying the formula each time I need to update values. This is the formula I currently have

=IF(ISNUMBER(SEARCH("First",A3)),"One",(IF(ISNUMBER(SEARCH("Second",A3)),"Two",(IF(ISNUMBER(SEARCH("Third",A3)),"Three",(IF(ISNUMBER(SEARCH("Fourth",A3)),"Four",(IF(ISNUMBER(SEARCH("Fifth",A3)),"Five")))))))))

enter image description here

3条回答
聊天终结者
2楼-- · 2019-01-29 06:55

Alternate without CSE or the volatile OFFSET function.

In B3 as,

=IFERROR(INDEX(F:F, AGGREGATE(15, 6, ROW(E$3:INDEX(E:E, MATCH("zzz", E:E)))/ISNUMBER(SEARCH(E$3:INDEX(E:E, MATCH("zzz", E:E)), A3)), 1)), "")

Fill down as necessary.

enter image description here

For a case-sensitive lookup, change SEARCH to FIND.

查看更多
在下西门庆
3楼-- · 2019-01-29 07:00

Confirmed with ctrl+shift+enter

=INDEX($F$2:$F$20,MATCH(MIN(IFERROR(SEARCH(OFFSET($E$2,,,COUNTA($E$2:$E$20)),A2),LEN(A2))),SEARCH(OFFSET($E$2,,,COUNTA($E$2:$E$20)),A2),0))
查看更多
The star\"
4楼-- · 2019-01-29 07:01

Late answer...

=LOOKUP(2^15,SEARCH($E$3:$E$8,A3),$F$3:$F$8)

查看更多
登录 后发表回答