Exact Vlookup not always returning match...

[jlhalliday1]

I'm trying to show why we need to use exact match when we have ID's that are case sensitive…first tab contains 4 records, 2 similar account numbers that are different with case sensitivities.

Each time I change the sort order of the data on sheet two, the changes to rows 2&3 (=VLOOKUP($A2,Sheet2!$A:$F, 2,FALSE)) are expected…excel returns the values of the first record that "matches" the criteria.

However, rows 4&5 (=IF(EXACT($A5,VLOOKUP($A5,Sheet2!$A:$F,1,FALSE))=TRUE,VLOOKUP($A5,Sheet2!$A:$F,2,FALSE),"No exact match")) should always pull the correct EXACT data, but they don't (change the sort on sheet 2 based on Account Owner from A-Z to Z-A and see what happens) …can anyone tell me why?

Jan

 

[Luke M]

Reason why:
VLOOKUP is always returning the first result from the table. When you sorted the table in reverse, it found the lower case version first, but then that is not an EXACT match to what you have in col A. As you've proven/asked, VLOOKUP is not a good way to find a value is case-sensitive is an issue.

Solution:
On Sheet1, cell B2, enter this array formula:
=IFERROR(INDEX(Sheet2!B$1:B$26,MATCH(TRUE,EXACT($A2,Sheet2!$A$1:$A$26),0)),"No exact match")
Remeber to confirm array with Ctrl+Shift+Enter, not just Enter. Then copy down and to the right as needed.

Now the EXACT function is doing out lookup-type work, and we can sort the data.

 

[Balli]

V Lookup is not case sensative. Here u need to use some different formula as below:
INDEX(Sheet2!$B$2:$B$26,MATCH(TRUE,INDEX(EXACT(Sheet1!A2,Sheet2!$A$2:$A$26),0),0))

A sample file is attached. I hope, this will help you.

 

[jlhalliday1]

Thank you BOTH very much! I knew VLookup wasn't the answer, but couldn't get EXACT to work properly...
I've never worked with Indexing, but it looks like I'm going to learn and that in the case of SFDC it will become my new best friend!

Thanks!

Jan