Must be an easier way then a Nested If....

[jackfrostog]

Hello Everyone,

First time posting, but have learned a lot since finding this forum. Now my issue is basically finding certain words within a field and assigning it the code that corresponds to that word. Pretty simple but the problem is my database for the words & codes is ever growing and the only way I got the statement to filter through all the words was a couple of 63 lined Nested If's (rows 2 -> 64 on formula, 65 -> 127 the second, so on & so forth) I'm assuming VBA would have a simple solution with arrays involved but I haven't learned that yet (Been meaning to get into it but life has been heck-tick) So would there be a formula solution to this?

Thank You,
Jack J.



Sample Nested If Statement.xlsx

[XOR LX]

Hi,

You can do all that searching by combining it all into one array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER). So, in B2 and copy down:

=IFERROR(INDEX(Sheet2!$B$2:$B$10,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,Sheet1!A2)),0)),"")

Edit: in fact, you can do this with a non-array version as well (preferable):

=IFERROR(INDEX(Sheet2!$B$2:$B$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,Sheet1!A2)),,),0)),"")

Regards

[jackfrostog]

Worked like a charm!! Thank you!! Never really thought of using Index in that way!!

[RobertMika]

The only trouble with similar questions is to get the right word.
What if yo had Swig?
(BTW your Wig in Sheet2 has space after g so is not found by formula)

Look here for VBA solution that take Exact word:
http://www.excelfox.com/forum/f22/fi...ther-word-603/

[XOR LX]

"Worked like a charm!! Thank you!! Never really thought of using Index in that way!!"

You're welcome, and yes, it certainly is a surprisingly flexible beast, INDEX. To quote Daniel Ferry at the excellent Excel Hero site:

"From my perspective, the Excel INDEX function is the single most important in the roster of Microsoft Excel functions."

Regards