1

To make things easy for users of this workbook, I've setup column J with data validation (pulling values from Sheet2) that allows for multiple selections of event types (Academic Calendar, Art Exhibits, etc).

I had to use some code to pull that off, but I found a YouTube video that walked me through it so my lack of excel coding experience didn't hold me back.

In order to import this sheet into another program, I can't have "Academic Calendar" as the Event Type, I need it's corresponding code, "et-acacal". The corresponding code is sitting in the next column over on the sheet containing my event types.

Essentially I need to replace every instance of "Academic Calendar" with "et-acacal" and "Art Exhibits" with "et-artgal" as well as the various combinations of event types like "Academic Calendar, Art Exhibits" with "et-acacal, et-artgal"

I've figured out a really crappy way to pull this off, by using multiple SUBSTITUTE functions (see below), but it was tedious to set up and I have to believe there's an easier way to do this. I only had 30ish different event types, what happens if I had/have 100 or 200?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,Sheet2!$A$30,Sheet2!$B$30),Sheet2!$A$29,Sheet2!$B$29),Sheet2!$A$28,Sheet2!$B$28),Sheet2!$A$27,Sheet2!$B$27),Sheet2!$A$26,Sheet2!$B$26),Sheet2!$A$25,Sheet2!$B$25),Sheet2!$A$24,Sheet2!$B$24),Sheet2!$A$23,Sheet2!$B$23),Sheet2!$A$22,Sheet2!$B$22),Sheet2!$A$21,Sheet2!$B$21),Sheet2!$A$20,Sheet2!$B$20),Sheet2!$A$19,Sheet2!$B$19),Sheet2!$A$18,Sheet2!$B$18),Sheet2!$A$17,Sheet2!$B$17),Sheet2!$A$16,Sheet2!$B$16),Sheet2!$A$15,Sheet2!$B$15),Sheet2!$A$14,Sheet2!$B$14),Sheet2!$A$13,Sheet2!$B$13),Sheet2!$A$12,Sheet2!$B$12),Sheet2!$A$11,Sheet2!$B$11),Sheet2!$A$10,Sheet2!$B$10),Sheet2!$A$9,Sheet2!$B$9),Sheet2!$A$8,Sheet2!$B$8),Sheet2!$A$7,Sheet2!$B$7),Sheet2!$A$6,Sheet2!$B$6),Sheet2!$A$5,Sheet2!$B$5),Sheet2!$A$4,Sheet2!$B$4),Sheet2!$A$3,Sheet2!$B$3),Sheet2!$A$2,Sheet2!$B$2)
0

The best way to manage this is using the VLOOKUP function. This will look up the relevant event code for a event name in cell J2.

=VLOOKUP(J2,Sheet2!$A$1:$B$999,2,0)

However, you have the added complication of multiple criteria in the same cell. To achieve a VLOOKUP on multiple criteria in the same cell would require you to replace one overly complicated nested SUBSTITUTE function with another - given that's why you're asking, I won't go into how that works (lots of unwieldy IFs,MIDs and SEARCHes).

Your best bet is to try and reorganise your spreadsheet so you're not trying to get all your criteria into a single cell. Excel isn't really built to do that - the cell is meant to be the most basic unit (hence the name). Try and use multiple columns and do VLOOKUPs for each one.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.