Excelgoodies - Insights from Microsoft Excel Power Users
Excelgoodies (EG) is a leading Microsoft Excel Training and Consulting service provider. Excelgoodies specializes in Microsoft Excel, VBA Macro Programming, Advanced analytics using Excel PowerPivot and Financial Modeling.
The syntax for Vlookup is: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The Syntax for Index is:
INDEX( array, row_number, [column_number] )
The Syntax for Match is:
MATCH( value, array, [match_type] )
Using Vlookup In the above table we can find EmployeeName & Dept with the help of EmployeeNumber and Dept with the help of EmployeeName, however we are not able to find EmployeeNumber with EmployeeName and Dept because the Vlookup will work only from the right to its value and not to the left.
To fix this issue we use Index & match function.
Use Index first and insert Match function into it.
=Index(Data Range, #,Column number)
“#” indicates the row number to which it is referring to. So use Match function instead of “?”
The number which is in the last in the index can help you with EmployeeNumber for 1, EmployeeName for 2 and Dept for 3.
There’s a calculator in Excel 2010 but by default this feature is hidden, to get the calculator on your Excel screen navigate to Quick Access toolbar options – it’s the down arrow symbol next to the undo button on the top left-hand of the screen – and click More Commands.
Adding a calculator hot link to Excel
It will lead you to Excel Options dialog, under Choose commands, click drop-down button to select All Commands. Now scroll-down to find the Calculator command and click Add >> to show it in Quick Access toolbar. Hit Ok to continue
Click Calculator button in Quick Access toolbar to immediately open Calculator. Now you can use it for simple calculations.
The below code will add serial numbers up to 500 in your sheet and along with it will show progress on the status bar.
Application.StatusBar= "Start Printing the Numbers"
For icntr= 1 To 500
Cells(icntr, 1) = icntr
Application.StatusBar= " Please wait while printing the numbers " & Round((icntr/ 500 * 100), 0) & "%"
Explore extensive VBA Automation Course here, to automate your routine Excel Reports, Processes and Tasks using Microsoft Excel VB Programming.