Excel Tip - Six Common Mistakes Made When Using VLOOKUP Function In Excel

Now and then your VLOOKUP recipes may not create the outcomes that you anticipate. Here are my main 6 reasons that an amateur VLOOKUP client finds their equation isn't doing what they anticipated.

1. The issue of the Left Hand Column. VLOOKUP can ONLY look right. Along these lines it is very limiting. A great deal of clients will reorder sections to oblige this standard.

The answer for this includes not utilizing the VLOOKUP work. Utilizing a blend of the INDEX and MATCH elements of Excel is a typical option to VLOOKUP. It is undeniably progressively adaptable and truly opens up increasingly potential in utilizing Excel.

2. You Need An Exact Match. The last piece of the VLOOKUP equation expects you to indicate a definite or an on the other hand a surmised match.

=VLOOKUP(value,table_array,column_index_number,[range_lookup])

For the most part Excel clients search for a definite match and in those occurrences the worth FALSE or zero should be gone into this piece of the equation. In the event that you leave it clear, at that point the default worth is TRUE, which means it will search for an inexact match. This is the place the mix-ups can be made.

There are various conditions when you would search for an estimated match from a VLOOKUP, for instance on the off chance that you are taking a gander at the business commission section or banding. All together for the VLOOKUP to work the TRUE qualities must be arranged in rising request

So on the off chance that you are searching for a precise match - ensure you enter the worth FALSE in range_lookup.

3. Wrong Column. The column_index_number some portion of the recipe is the segment from which the worth are gazing upward is returned. This piece of the equation is truly not dynamic and can return inaccurate worth if an additional section in embedded into the worksheet in the region where your information is put away.

In this way, there are a couple of moves you can make to guarantee this does not occur.

Lock the worksheet - this will anticipate clients making changes. This isn't generally a suitable arrangement if clients do need to change the worksheet.

Utilize another capacity with your VLOOKUP - that is the MATCH work, embed this into the col_index_num part or contention of the recipe.

4. Hauling Formula Error. This is on eof the most widely recognized reasons that a VLOOKUP isn't restoring the outcomes you anticipate that it should. We can fix this issue by make the table_array outright by enclosing it by $.

5. Information Source Has Expanded-additional columns of information have been included. This is again one of the more typical reasons I see. Basically there has been more information added to the information source and the table_array part of the equation has not been refreshed. There a two or three different ways around this

Convert your information source to an Excel table. Home Tab-Styles Group-Format As Table

Continuously go to information source and hit CTRL+A to get the full informational index revived.

In the event that conceivable I utilize the main strategy and utilize the information source as a table.

6. You Data Contains Duplicates. The VLOOKUP capacity can just return one record. It will restore the primary record that matches the worth you searched for in your table cluster. In the event that your information has more than one conceivable query esteem, at that point VLOOKUP isn't the capacity you need as of now. I recommend a Pivot Table is utilized as an option.