Hello,

I use Excel frequently at work but am by no means an expert or even very good.

The following formula works for what I want it to do, but I expect there is probably a way I could condense it:

=IF(NOT(ISERROR(MATCH(E6,I86:I92,0))),"ON",IF(NOT(ISERROR(MATCH(E6,I93:I99,0))),"WEST",IF(NOT(ISERROR(MATCH(E6,I100:I105,0))),"EAST",IF(NOT(ISERROR(MATCH(E6,I106:I108,0))),"QUEBEC")))))

Essentially, I am searching a cell (E6) against a range (I85:108) and looking for a match. Depending on which cell it matches, I need the formula to generate the region (ON/WEST/EAST/QUEBEC). I am using a similar formula to drive various formulas throughout my sheet.

If anyone could give me advice on the above, I would be very appreciative.

Thanks !

Little late perhaps but here's what I can see ....

So from what I understand you're searching specific ranges (86 - 92, 93 - 99, 100 - 105, 106 - 108) for whatever is found in E6? When it finds a match within the selected range it spits out the corresponding On / West / East / Quebec?

Overall it looks pretty good. I think you can probably clean it up a little though. Using Not(iserror seems inefficient to me but I haven't tested it for myself yet.

Is it important to identify them within certain ranges or is there a better way to list your data perhaps to clean up your formula?

Feel free to PM a sample data set in a google doc and I'll see what I can help you with. Worked as a glorified excel nerd for the past couple years in data analysis, learned a few tricks I'm happy to share if they help.

If you have a sample or need any excel help in the future pm me. I maintain about 20 spreadsheets at work.

This post was edited by Weeknd on Jun 23 2017 04:05am