Require an Excel Formula for Case Sensitive VLOOKUP

[former_member392959]

Hi Guys,

In EWM (and CRM & F&R) there are certain tables that make use of GUID's which are case sensitive. So the same GUID can appear multiple times in a table but with different letters in upper-case or lower-case. When doing a normal VLOOKUP it is not case sensitive so the formula returns the incorrect value which is usually the first match it finds from top of the list.

If you have a formula that can help me solve this LOOKUP problem, it will be appreciated.

See examples in attached Excel Spreadsheet pic.

Thanks in advance.

[former_member392959 · Accepted solution]

Hi All,

My colleague assisted me with a formula to solve this case-sensitive LOOKUP. See below.

=INDEX($B$2:$B$9;MATCH(TRUE; EXACT(E2;$A$2:$A$9);0);0)

You need to use the EXACT function, as this is case sensitive. You can use this together with the INDEX and MATCH function. Where column B is the source of the article you want returned, E2 is the cell you are comparing, and column A is the column containing the GUID that you are comparing your values to. Very important: this includes an array formula, so this formula will only work if you press 'Shift' + 'CTRL' + 'Enter' while in the cell itself.

Cheers!

[Former Member]

This message was moderated.