#### benjennings

##### New Member

- Joined
- Mar 4, 2008

- Messages
- 32

Within a system I have a table (mapping table) that contains the rules of looking up a value and maps to an account code. I'd need to replicate this in Excel but am having difficulties with the formulas.

My table is in 3 columns, like this:

WhereClauseType (Col A) | WhereClauseValue (Col B) | Target Account |

Exact | 1111-000 | 241100A |

Like | 1111-??? | 241100B |

Between | 1111-000,111-899 | 241100C |

<tbody>

</tbody>

I need a formula that returns the Target Account by looking up a value from this table, however with a criteria that it looks for an "exact" match first, then a "between" match second, then lastly a "like" match.

For example:

lookup value 1111-000 returns 241100A

lookup value 1111-050 returns 241100C

lookup value 1111-916 returns 241100B

lookup value 1122-000 returns #N/A (as it doesn't fit in any criteria)

Many thanks

Ben