Here you are going to learn the way how we have to fix the issue when your Vlookup is not working. If you would like to learn about VLOOKUP in detailed click here. First you need to the error types that occur in excel. First one is syntactical error and the second one is logical error for any formula these are the main error types occur. Hence first check syntax of the VLOOKUP that you have entered in your sheet. It should be like =Vlookup(lookupvalue, tablearray, columnindexnumber, false)
Lookupvalue: The value which you need to search in particular data range.
TableArray: This would be your lookup value related column to result existed column (Example: if you have lookup value in ‘A’ column and results are in column ‘C’, then your table array would be A:C.
Column Index Number: This is your result column number in the above example, your column index number is 3. Because A is first column, B is second column and C is your result column that is 3rd column.
False: This indicates zero always (that is known as approximate match)
Hence in the above example if we consider, we can get syntax in the below manner
if you are looking result in the same sheet
if you are looking result in the different sheet then you need to add sheetname in the table. Please find below for syntax for your reference
If you miss any thing in the above for any scenario, you get an error that would the syntactical error.
Now let’s understand logical error. If you enter syntax correctly still if you are getting error as “#N/A” then your vlookup working correctly. Still you need to check whether you have selected correct column or not in the respective syntax line. For example in the above described example. Instead of A:C , you selected B:C in the table array. that would be logical error and you will not get results. Because you have not specified look value related column in the table array.
If you enter column index number as 4 in the above example, then also vlookup throws an error. Then you need realize that you have not give correct number to lookup the value in the formula. Then your error would be #VALUE.
if you enter 1 in the last block instead false or zero, then also your formula throws an error. This is how you need to figure it out to work your vlookup correctly.
There are several ways to find last row in the excel VBA. Finding last row is very important in the Excel VBA programming to work with ranges in spreadsheet. Based on your user or client requirement, you can use the below any one of the method in order to get last row value into one variable. The same can be used in particular worksheet in excel macros.
In case if you are going to update data in the worksheet by yourself as part of your automation, you can use this method to get last row of the particular excel spreadsheet. However this method can be used only when you create new worksheet or created by your program related worksheet.
Dim Lastrow as long
If worksheet name is Summary then, you can define as shown in the below
Advantages of Usedrange.Rows.Count :
There is no requirement to specify any particular column or column letter to grab last row in this method. You can directly assign to one variable and use the same in the respective worksheet.
Disadvantages of Usedrange.rows.count:
If you use this predefined method in existing input excel spreadsheet, there are chances to get incorrect last row. Sometimes if excel spreadsheet downloaded from web portal or any other source, you get some garbage values in sheet.
Using this also you can get last row and use the same in entire VBA program for the respective worksheet. To use this, you should have non blank cells in any one particular column range. Then we can use that column number in this method as shown in the below example
Dim Lastrow as long
(Note: Better to define lastrow as long. It is best practice, because number of rows can be exceeded integer datatypelimit. If you know that rows compulsorily lesser than 32K, then you can go with integer)
I am assuming 1st column has non blank cells within the range)
Advantages of cells(rows.count,columnnumber).end(xlup).row:
This method always provides you accurate value of last row in particular column range. Hence you can use this in input files in excel macro or VBA.
Disadvantages of cells(rows.count,columnnumber).end(xlup).row:
We have to take care to use non blank cells column. Else there is a chance to get incorrect value into last row.
In this method also we should have at least with column range with non blank cells.
Dim Lastrow as long
Note: Here I consider column “A” has non blank cells.