-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
ignoring 1 word when looking for the most common word
Excel Facts
Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Implement the formula in P2 or in F9...
If in P2...
=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($P$2:$P2))),"")
If in F9...
=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($F$9:$F2))),"")
If any other cell of your liking...
=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($1:1))),"")
Upvote
0
Patcheen
Active Member
- Joined
- Sep 28, 2015
- Messages
- 388
- Office Version
- 365
- Platform
- Windows
thank you for all your help
this is the one im using it works a treat thank you
=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($1:1))),"")
this is the one im using it works a treat thank you
=IFERROR(INDEX($E$9:$E$31,INDEX(MODE.MULT(IF(ISTEXT($E$9:$E$31),IF(ISNA(MATCH($E$9:$E$31,{"","unknown"},0)),MATCH($E$9:$E$31,$E$9:$E$31,0)))),ROWS($1:1))),"")
Upvote
0
