Vlookup on multiple columns

First read article on simple vlookup in Excel

Many times there is need to compare two columns from the lookup table. Consider a table which contains employee data of different companies. Employee number would be unique to company, so we need to match company as well as empoyee number to get the designation.

Here you need to concatenate the key columns in the lookup table and form another column. Also for lookup value we have to concatenate company and emp no.
Formula for concatenate =CONCATENATE(A2,B2)

Now we need to lookup value for key column in the table.
Vlookup will look like this.

 =VLOOKUP(C2,'Emp Details'!$C$1:$F$9,4,0)
 Note: Table_array mentioned here starts from the column key and not from the column company and emp no. This is because vlookup always looks up for the value in the first column of the table array.
Final output excel will look like this.

How to do VLOOKUP in MS Excel

Here i will consider a simple database to look up values from. Considering employee details as an example. Currently considering only 4 fields and 4 values, there may be case where there are hundreds of records when you actually need to do VLOOKUP.

Suppose there is another sheet where you want designation in second column against the employee number.

Select the cell where you want to write the vlookup i.e. where you want the designation and click on the insert function button. Here i suggest that if you are new to formulas, use this to create or edit formulas rather than writing everything.

Select vlookup. A pop will appear for your inputs. There are four fields that you need to input amongst which 3 are mandatory and 1 is optional.

Lookup_value is nothing but the value you want to find in the database. It can be a value, reference or a string. Here in our case we need to lookup for employee no. We will give reference of A2.

Table_array is nothing but the table you want to lookup in. Here you need to make sure that your table is sorted by your first column and your first column is the field that you want to lookup for. i.e. in our case the table should be sorted by employee no and should be the first column in our table.

Also to make sure the table reference does not change when we copy our formula to other location, just press F4 once after selecting the table.

Col_index_num is nothing but the column number that you want to get back into the current cell when a match is found.

Range lookup can have two values 0(false) or 1(true). 0 means exact match should be returned in vlookup or 1 means match should be approximate.

Press ok and you will see the value in your cell.

Since we need to get values for all the rows, we need to have similar formular in all cells. We will not write the formula again but simply copy the cell and paste in all the other cells.

Final Formula: =VLOOKUP(A2,'Emp Details'!$A$1:$D$5,4,0)

Tip: While writing a formula the fields that look bold are mandatory and the one which is not bold is optional.


Simple vlookup.

vlookup help

Debugging A Background (Batch) Job In SAP

Very often we come across the situation where we have to debug a report or a particular transaction which is run in background (Batch job). Debugging and analyzing the same when a job is scheduled cannot be done by conventional way. Follow the below steps to debug for a background job. Here we will take an example of a custom report, schedule a job & debug the same.

è  Enter the custom transaction.

è  Enter all required fields on the selection screen.

è  Press F9 directly or Go to program and click Execute in Background

è  A popup screen will appear, press ok once.

è  A screen will appear asking for start time.

è  Click on Date/Time and enter near future date and time. Note that this time should be relative to SAP system time and not your desktop system time. Then press save button.

è  Information message will pop up saying background job has been scheduled.

è  Now go to transaction SM37 (Background Job)

è  Select your job and in the command box type JDBG (Note you only need to type JDBG and no /n or /o)

è  And your debugging will start up as soon as you press enter. (You might have to press F7 to reach to the start of your report)

Pop Up To Select Radiobuttion Dynamic Function Module

Here you need to create a custom function module to get pop up to select radiobuttons.

Here in below function module I have put facility for 6 radiobuttons. Depending on the texts entered radiobuttons will show up.

Import parameters:

Export parameters:

Function module source code ZPOPUP_RADIOBUTTONS:

Function module modal screen 5100: 

Function module top include LZPOPUPTOP:

Funciton module subroutines include LZPOPUPO01:

GUI Status 5100:

 

GUI Title 51:

Visual Basic Editor Shortcut MS Excel 2007

When editing macros or when you are working with VBA code and creating forms in MS excel, you have to navigate to the Visual Basic Editor too often. Follow the simple steps below to add Visual Basic Editor as shortcut to quick access toolbar in MS excel.

è  Click on office button.

è  Click Excel options right at end of the menu on right side.

 

è  Click on customize

è  From choose commands drop down select developers tab

è  Select Visual Basic and Click on add button.

è  Finally press ok to close the options.

Note: Alternatively you can also use shortcut key as ALT+F11 to start Visual Basic Editor.

Comments in HTML

Often we come across situation while writing blogs where we need to add comments. Many times you just need to add the snippets from other different sites in the same template you created. But the confusion comes that how do i distinguish between codes if i refer in future and need to change / add or remove something.

Answer to this is simply adding comments in the HTML code that you add. Next question that comes is how to do this. This is pretty simple, you just need to add a comment link with open tag as <!--  and close tag as -->. Anything writen between this would be considered as comments.

For example: <!-- Code for google analytics -->
The above comment line gives me an information that i had added the code for google analytics.
Just add the above line and write your own comments, the way you can understand in furture.

Fixing Error Internet Explorer cannot open the Internet site

Internet Explorer cannot open the Internet site <site name>

Operation Aborted.

Did you encounter this message then go ahead and read the article to fix this.

You need to change you internet security settings to avoid getting this message.

è  Go to tools

è  Open Internet Options

è  Click on security tab

è  Click on custom level

è  Disable “Active Scripting”

è  Disable “Scripting of Java Applets”

Note: This will disable some of the features of the website to you. Alternatively you can open the webpage in other browsers such as Chrome or Firefox. You will not get an error. You can inform the site administrator for such an error for them to fix this permanently.

Internet Explorer Keyboard Shortcuts

Below are the keyboard shortcuts that really help navigating through internet explorer while browsing.

ALT+D	Cursor moves to the address bar & you can continue typing the new address.
CTRL+E	Cursor moves to the search box & you can continue search with your default browser.
F11	View internet explorer  in full screen
ALT+HOME	Go to your Homepage
CTRL+TAB	Switch between different tabs
CTRL+F	Find on the page
F5	Refresh the webpage
ESC	Stop loading the page
CTRL+N	Open a new window
CTRL+W	Close the current tab
CTRL+S	Save the current webpage
CTRL+I	Open favorites box
CTRL+H	Open History box
CTRL+T	Open new tab

These are the general shortcuts that i have been using. Please comment if you have used any other shortcuts than mentioned above.