Monday, December 23, 2013

Making Excel VLOOKUP Function to Work


I have seen many times that there are some scenaries where the Excel VLOOKUP function is not able to calculate the value we are looking for. For example, let's search the amount for the ID 16 according to a little table with this value.




This is something really easy to do, we need only to use VLOOKUP function selecting the ID, the matrix and the column to be search.




However, when you see the result of this function you notice that there's an error despite the values are fine.




The reason of this error is that the searched ID value is stored as text, so you can convert it in number in this case because it's only one value, but what if it were a thousand of them?.




To avoid worrying about format issues just embraces the key value in the VALUE function inside the VLOOKUP function.




Once you use the function you are going to get the value that corresponds to the ID.




Consider that this is an example where the VLOOKUP function won't work as we want, I helped many people with this to solve another kind of problems with this function.

2 comments:

  1. mate, thnx! I like it! That is an extremely essential Excel function and this is very good explanation. You should even do something for the Index Match that does basically the same thing of the VLookup ;)

    ReplyDelete