• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match formulas > case sensitive IDs

R

rfreeman

New Member
I have been using vlookups for most of my day to reporting tasks, but am coming across some errors, and a colleague said using Index/Matching formulas is much better.


A problem I have is with the below formula because the IDs are slightly case sensitive i.e. "001a000001KYl9a" refers to a different account than "001a000001KYl9A"


Formula belwo causes invalid data errors

=VLOOKUP($B:$B,'[Sales Report]Accounts'!$B:$B,2,FALSE)


How do I transfer this into a Index/Matching formula
 
Hi ,


The errors in your formula are not because VLOOKUP is inferior to INDEX/MATCH ; the issue is just the syntax.


The third parameter in the VLOOKUP function is supposed to refer to a column number within the range specified as the second parameter , so that when a match is found in the first column of the range , the corresponding value from the specified column is returned.


To give an example , suppose the second parameter is G7:Z23 ; this is a range which is 20 columns wide ; to return a valid result from a VLOOKUP formula using this range , the third parameter can vary from 1 through 20 ; a value of 1 will return the matching value itself , which is not of much use ; practically therefore , the third parameter can vary from 2 through 20.


In your example , the range is just one column ; specifying 2 as the third parameter will result in an error.


Secondly , the first parameter of the VLOOKUP is supposed to be a single value or cell reference , not an entire column reference. Of course , as long as you do not enter it as an array formula , Excel will not complain , but will take the value in column B in the same row as the cell where you put this formula in ; this may or may not be what you wish to do.


It is always preferable to specify one single value , and concrete range references e.g.


=VLOOKUP($B$13,'[Sales Report]Accounts'!$B4:$C1050,2,FALSE)


Case sensitive lookups


See these links for more information :


http://support.microsoft.com/kb/214264


http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-case-sensitive-lookup/


Narayan
 
You must log in or register to reply here.
Back
Top