Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Excel VLOOKUP Function for Your Business

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 272

PostPosted: Thu Oct 12, 2017 12:24 am    Post subject: Excel VLOOKUP Function for Your Business Reply with quote

VLOOKUP or 'vertical lookup' is one of Excel's frequently used lookup and reference functions. In general, it looks for a 'value' you want to look up from a table or a range, and when found, returns that value.


VLOOKUP function has four parameters or arguments in its syntax. The parameters are separated by commas.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])


lookup_value: the value you want to look up or searching for

table_array: the range within which your specified value is located

col_index_num: this is the column number in table_array that contains the return value.

range_lookup: here you specify the logical argument TRUE or FALSE. If you specify TRUE, it means you are looking for an approximate match for your return value. But if you specify FALSE, that means you want an exact match for your return value.


In the following example, in my Excel sheet named Solar, I have infos about different solar panels, number of cells and unit price. I have only 4 models here just to show you how VLOOKUP Function works. In reality you could have hundreds of data.





Now, in my Excel sheet 1, I want to show infos about cell numbers (in Column B) and unit prices (in Column C) when I enter the model number in Column A.

So in Cell B2 in Sheet1 type

=VLOOKUP(A2;Solar!$A$3:$D$6;3;FALSE)


It means, when you type the model number in Cell A2, it looks for the model info within the table in sheet Solar where you have fixed the range (A3:D6) with absolute reference using the '$' sign ($A$3:$D$6), then goes to Column 3 which contains the return value (in this case, number of cells). As the 4th parameter is FALSE, so it returns you the exact match it finds in Column 3.





Now once you finished typing the above formula in cell B2, press Enter. You will see #N/A as you haven't entered any model number in Cell A2. Now copy the formula down the column B as per your requirement. It will be filled with #N/A. Don't worry about it. We will make it clean soon Smile


Now in Column C of your Sheet 1 you want to show the unit price as well, when you enter the model number in Cell A2. So like in Cell B2, type the following in Cell C2

=VLOOKUP(A2;Solar!$A$3:$D$6;4;FALSE)


Look, here the Column number is 4, as the 4th Column in Sheet Solar contains the return value for unit price. Now copy the formula down the Column C as per your requirement. Again it will be filled with #N/A. Don't worry Smile






How to avoid the #N/A error value

So when the VLOOKUP Function doesn't find the exact number or match to return, it returns you the #N/A error value. It makes your Excel sheet clumsy. To avoid this, you can wrap the VLOOKUP Function within the IFERROR Function.

So enter in Cell B2

=IFERROR(VLOOKUP(A2;Solar!$A$3:$D$6;3;FALSE);"")

Copy it down the column. You will have a clean column without the #N/A errors.



Do the same for the prices in Cell C2

=IFERROR(VLOOKUP(A2;Solar!$A$3:$D$6;4;FALSE);"")

Again, copy it down the column to avoid the #N/A errors.


Take care of the column numbers in the formula. In cell B2 it is 3, in cell C2 it is 4.

Now in any cell in Column A you enter your model number, corresponding solar cells numbers and prices are shown in Column B and Column C.





Have fun Smile

Cheers!!!





*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.


For comments of suggestions, please contact us: info@shamskm.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group