Automatically Populate cells in another column based on search function

[Kyle Cornelius]

Hello all,

I'm Kyle and I'm working on a spreadsheet with over 12,000 items of obsolete inventory. I'm trying to categorize these items into product classes. As you can imagine with that many items it's taking some time. Is there a way I can enter a search for a word like "bolt" or "washer" and have the spreadsheet automatically find those and fill in the cell in the column next to them with an identifying product class number? That would be much more efficient than doing them one at a time.

Thanks

 

[Luke M]

Let's say you built a lookup table in D1:E10, with the correlations, like:
Bolt____A123
Nut____B345
....
Screw___D987

Then, with your data in A2, put this formula in B2:
=IFERROR(INDEX(E:E,(1/MIN(IF(ISNUMBER(SEARCH($D$1:$D$10,A2)),ROW($D$1:$D$10))))^-1),"")
Confirm this formula as an array using Ctrl+Shift+Enter, not just Enter. Copy formula down for all 12,000 items, and you're done. Go grab a cold one, and relax.

PS. If you want to make this case-sensitive, replace SEARCH with FIND.

 

[Kyle Cornelius]

Thanks Luke!