Select Page
How does VLOOKUP formula work in MS-EXCEL

How does VLOOKUP formula work in MS-EXCEL

What is a v lookup in excel?

VLOOKUP is a popular formula in Microsoft Excel. It is basically used to find value within a particular cell with respect to a reference cell value in a large table.

Steps to write Excel vlookup formula:

Today we shall see how to do with this VLOOKUP formula.

Syntax of Vlookup :
 VLOOKUP (lookup_value, table_array, column_index_number, [range_of_lookup]) 

First of all , open an excel sheet and create a table of Product Detail, where Purchase Cost, MRP etc. are mentioned (Same as in the picture).

what is av lookup in excel,formula vlookup,what is av lookup,excel vlookup formula.


Below the table, select a cell and write- Type Product Name and beside that cell write –Purchase Rate.
So, we want to find out the purchase rate of product name, which we will type under that first cell. Here in the picture you can see that the Cell ID of that cell is – B10.

vlookup excel formula,excel vlookup example,vlookup excel function,vlookup function,vlookup example,vlookup excel example

And we have written the vlookup function in the cell, under Purchase Rate as –
=VLOOKUP (B10,A2:E7,4,TRUE)


Here B10 is the Cell No. of that place, where we shall type the product name.
A2:A7 is the table array range .You shall get this value, just after selecting the table.
4-It is the 4th column i.e. the column of Purchase Rate.
TRUE-Its used for approximate match(for exact match use FALSE).

That’s it. Now press Enter and required value will be shown in the box.Now change the product name . Then the Purchase rate will also be changed simultaneously.

Also Read:

How to do ‘Merge Cells’ in Microsoft Excel.

How to do ‘Merge Cells’ in Microsoft Excel.

Merge Cells in Excel:

Merge Cells functionality is very useful at the time ,when you want to create a Header Column and under that column you want to set multiple child columns. We shall see ‘ how to merge cell in excel‘ in the following paragraphs.

How to merge cells excel:

For an example if you want to merge the ‘Product Details’ Column, shown in the Picture, then you’ve to follow the steps, provided below.

merge cell

Then select the ‘Product Detail’ row from the left side of the table and up to the end of that table.
As we want to merge all the header cells of the table we have to select all the cell of that particular table row. If you want to merge only two cells then select only two rows.

microsoft excel merge cell

Then click on the ‘Merge & Center’ button (‘Merge & Center’ button is within Home tab) Drop-Down menu will be opened. Select ‘Merge & Center’.

MS excel merge cell at center

Now, you will see that, the particular cell has been merged with other cells of that selected row.

complete  merge cell ms excel

Also Read:-

How to do ‘Voice Typing’ on Google Docs.

How to do ‘Voice Typing’ on Google Docs.

One of the very interesting features of Google Doc is its voice typing tool. In a busy scheduled day, you can ease your workload using Google Docs. Suppose you are in a conference or attending a lecture, so you want to take notes of the discussion. You can use Google Docs and take the notes at very fast.

Let’s see how to activate that thing in Google Docs.

Connect your microphone with your laptop or PC. Then log in at Gmail. At the top right corner, you will see a Google apps icon. Click on that icon. Then a Drop-down page will be opened with lots of Google app collection. On that drop down window, you can see an Icon named Docs. That’s it. Click on that icon.

google docs icon

You will be redirected to Google Docs Page. Then you’ve to click on the Blank document option to open a new blank document.

new google docs

Now, the blank doc will be opened with lots of tab and tools .

new google docs create, voice typing in google docs.

There is a tab, named ‘Tools’.Click on the Tools tab and from the drop-down menu select ‘Voice typing..’  

voice tools in google docs,voice typing

Now on the left-hand side of the Document, a Microphone ICON will appear. Now you have to click on that Icon.

microphone in google docs ,google voice typing

As soon as you click on that Microphone Icon, browser will ask your permission to allow interaction with Microphone. You have to click on Allow button on that popup.

google voice typing

Now it’s ready. Start speaking on the microphone and your words will be written on the Google docs automatically.

google docs writing, voice typing

Also Read:

Google Doc, an Emerging Giant

Google Doc, an Emerging Giant

Now-a day Google Doc has spread its wings over the market and day by day its growing popularity among the various professionals and students of schools and colleges, signifies that, this new technology to be a giant in near future.

In spite of the current fact that the Microsoft Word has vast features and day by day it is updated with the clients’ requirement, this new technology is going to the most important competitor of MS-Word. Some of the key reasons behind this new trend are discussed below.

google docs

Price

If you want to use the current updated version of MS-Word you have to purchase it from Microsoft. A majority of users of Word Processor are normal professionals, schools and college students. They do not need very high memory space to store data. And in Google docs you can do all this staffs at free of cost with 15 GB free storage. You just have to create a Google account to use this feature at online.

Cloud Environment

There is no headache of data loss even if your computer gets shut down at the time of writing; Google will store your work automatically to its cloud server. There is no need to save data manually. You can easily get access into your work space form anywhere in the world through internet.

Trust

Google is a trust worthy company with high skilled employee and any one can easily get convinced with its reputation all over the world. So there is a satisfaction with data security on the web.

Sharing Features

Google has very fast sharing capabilities. You can share your work with your colleagues, friend, team members and teachers etc at very fast speed. You can create a common shareable document, where anybody in the group can edit or add new work on the document.

Add On:

Google has huge add-ons collection; you can add those with your Google doc to make your job easier.

Also Read :-

How to do ‘MAIL MERGE’ in Microsoft Word – step by step guide.

How to do ‘MAIL MERGE’ in Microsoft Word – step by step guide.

Mail Merge is a powerful feature, added in Microsoft Word. You can use Mail Merge to print personalized form letters to multiple recipients.

What is mail merge in ms word ?

As for example, suppose you want to send interview invitation letters to multiple candidates .The content of all the letters are same but, few things e.g. recipients name, address, designation will be changed in each of these letters. In that case MS Mail-Merge will help you to do that thing.

So, let see how to do mail merge in step by step way.

Mail Merge In MS Word-Set Mail Format:

  • First of all open MS-Word and open a new Document and click on the Mailings tab.
  • Then Click on Start Mail Merge option. Select your desired format from the list, containing letters-Mail Messages, Envelopes and labels Etc.

Mail Merge In MS Word :Sample Letter Content

 

  • Now write the letter content within the document. Here we have written a document, inviting applicants for the interview.

To,
—————
—————

Dear ,


With reference to your application for the post of _ _ _ _ in our company, ABC Pvt. Ltd,

you are requested to come to our office for F2F interview on 20th January, 2019 .
You should bring one copy of your updated CV at the time of interview. For more
detail you can contact us at below mentioned Mob. No.

Thanks .

From,
HR .
ABC Pvt Ltd.
Mob No.-1234567890
Branch offices at –UK , USA & INDIA.

Mail Merge Word :Creating excel data source -contact name, address etc.


The very next step is that you have to create an excel sheet, containing candidates (or, employees) information such as name, city, country etc.

 

Mail Merge Word :Import Recipient data from excel sheet.

 

  • Now go to MS Word document and click on ‘Select Recipients’.
  • After Clicking on that tab a list(multiple options to import recipients data) will be opened.
  • You have to select ‘Use Existing List’.
  • Now windows data source dialogue box will be opened .
  • You have to browse that excel document (containing candidates’ data) and open it.
  • Now, another dialogue box (asking you to select the sheet no .) will be popped up on the screen.
  • Select the sheet no. and submit it.

 

  • So, the excel data have now been imported and you can use it for mail merge operation.
  • If ,you click on the option, named as Insert Merge Field; then, you can see a list, containing the columns name of that imported excel sheet.

 

  • Now you have to place the cursor at the appropriate position in the letter content and double click on those column name.
  • e.g. here, at the above letter body we want to import candidates name from the excel sheet and place it just under the word ‘To’. So we have to place cursor under ‘To’ and then click on ‘Insert Merge Field‘and then, you have to select ‘Candidate name’ from the list.
  • After you’ve placed the required columns name into your letter, click on the Preview Results button to see the final result.
  • Click on the right and left arrow, beside Preview Results button to see the all the imported fields.

Mail Merge In MS Word :Print or Send Letter.

 

  • After clicking on the ‘Finish & Merge‘ button you shall get options like Print Documents , Send Email Messages etc.
  • To print the document, you have to click on Print Documents and then print the files.
  • To send it as email letter, you have to click on Send Email Messages but, before that you have to set your email id at Microsoft Outlook first (if you really want to set mail merge in outlook then you can do this one.)

 

Also Read :-

 

19 interesting tips and tricks of Microsoft Word

19 interesting tips and tricks of Microsoft Word

Microsoft Word is one of the most popular Word Processing Software in the world. Even in this highly competitive era of digital technology, it still holds a significant place among the others.
Today we shall discuss about some tricks and beautiful features of MS Word. This Microsoft word tips and tricks, can surely make our writing tasks easier.

1.Create Cell using Keyboard:

  • Write +, then press TAB/Space-bar once,
  • Write + again, again press TAB/Space-bar,
  • Then write + again and press Enter.
  • A table with two cells will be created. In this way, if you increase + signs and spaces between them, the number of cells will also be increased.  

2. Change the lower case letter to upper case and vice-versa:

  • Select some lowercase letters and
  • Press SHIFT, then press F3 button.
  • Selected letters will be turned to uppercase letters. Follow the same steps for lower case letters and those will be turned to uppercase letter.

3. Copyright shortcut key:

  • To attach Copy right symbol with the text you have to press   Ctrl + Alt + c . Example of copy right symbols:   © Abc Pvt. Ltd , © 2019,McGraw-Hill Publishing Company Ltd.

4. Trademark Symbol Shortcut Key:

  • To add Trademark symbol after a word or phrase of words, you have to place the cursor just after the last letter of that word/s
  • Then press CTRL+ALT+T.  
  • Example- eBay™   .
  • In the same way you can add Registered Trademark also. Then you have to press CTRL+ALT+R .Example- Nestle® .
  •   If you want to add Service mark symbol after word/s do the following – write 2120 and press ALT+X. Example-Prime Diary™ Exclusive Paper Product.

5. Change font size using key-board:

  • To increase the font size select that word/s first .
  • Then press Ctrl +] buttons.
  • To reduce the font size select the word and press Ctrl + [ buttons.
  • Alternate  way to increase the font size- select the word/s and press Ctrl + Shift+>.  To  decrease the font select word/s and press Ctrl + Shift+<.

6. Underline words using keyboard

  • To underline a word you have to select the word first.
  • Then press Ctrl+U.
  •  To Double Underline a word, select the word and press Ctr+Shift + D .

7. Superscript and Subscript letters:

  • To add Superscript character you have to write the charter after the main word
  • Then you have to select the character and press Ctrl , then Shift and then + button .
  • In this case you have to use the + button, which is on the alphabet side of the keyboard.
  • To Subscript a character select the character first and then press the Ctrl button along with the button +, which is situated just above the alphabet portion of your keyboard.

8. Copy the formatting rules of any word and apply it to other words or paragraph:

  • Select the word/s and press Ctrl+shift+C  to  copy the associated format rules of that word/s.
  • Then select some other words in the Workbook and press Ctrl+Shift + V  to apply that copied format rules on those selected words.

9. Select Words one by one using keyboard:

Place the cursor at the beginning of the word and hold the Ctrl  key along with Shift key and then press Right Arrow(→) to select the word one by one .

10. How does ‘Go To’ button work:

  • In the Workbook screen press F5 button to open Go To box .
  • On the left panel (Go To what) of the box, several options are there. If you want to go to specific page then select page from the left panel and then enter the page no at the right side box .Then click ‘Next’. If you write +1 instead of specific page no. and click Next button then, the cursor will be shifted from its current page to next page.
  • In this way you can select Section, Line etc. and perform operation. You can perform Go To operation on Bookmark, Comment, Footnote, Table Graphic, Equation etc. also.

11. How  to open ‘Save As’  box using Keyboard.

In the Workbook press F12 and the Save As box will be opened immediately. You can write the name of your document and save it at any location.

12. How to check spelling:

  • To check the spelling of all the words you’ve wrote in the document, press F7 button.Then Spelling and Grammar Checker will be appeared on the screen.
  • If there is any error in the words then that word/s will be populated into ‘Not in Dictionary’ field and the suggested correct words will be listed in the ‘Suggestion’ box.
  • Select the correct word from the ‘Suggestion’ box and click on ‘Change’ button.
  • Then the wrong word will be corrected.
  • If any word, you think is correct but it’s not in the inbuilt dictionary then you can add that word into the dictionary after clicking ‘Add to Dictionary’ button.

13. How to split the document:

  • Suppose you have already completed writing in the first three pages .Now you have just started to write on the fourth page but, now you want to check some old data on the first page. So you have to scroll up to first page again to check those things.
  • In this scenario you can split the Workbook into two or more sections and set the first page in any one of the first section and do the writing on the other section. To split the word document press Alt+Ctrl+S .

14. How to insert Current Date and Time in the document:

To insert current date  press Alt+Shift+D. To insert current time press Alt+shift+T .

15. How to select a line using keyboard:

  • To select a line at a time, you have to place the cursor at the beginning of the line and press Shift+Home.
  • Suppose the cursor is at the end of the line and you want to select that line then press Shift+ END .That line will be selected from the end to the start of the line.
  • You can apply these shortcut keys even if the cursor is at the middle of the line, but in that case cursor’s primary position will be treated as, start of the line for the 1st technique and end of the line for the 2nd technique.

 16. Shortcut techniques to draw Lines

  • Write hyphen ( – ) three times and press Enter , a thin straight line will be created.
  • Then write underscore ( _ ) three times and hit Enter, a thick straight line will be created.
  • Now write Equal sign (=) three times and hit enter; more thick straight line will be created.
  • In the same way write * (asterisk symbol) , # (hash symbol),~(tilt symbol) to create different pattern of line.

17. Format a rectangular portion of a paragraph using mouse and Alt Key.

Press Alt then drag mouse over the paragraph to select particular portion and then change the format. Change color, font etc. of the selected area.

18. How to delete the next and previous word to the cursor.

Press ‘Del’  button to delete the next word of the cursor. Press Ctrl+ Back space to delete the previous word of the cursor.

19.  Select lines one by one using keyboard.

The next Microsoft word tips and tricks is on selecting line one by one by keyboard.For this do the following .

  • Place the cursor at the beginning of the line and press F8.
  • Then press up/down arrow to select line one by one.

Pin It on Pinterest