GST Course

Share on Facebook

Share on Twitter

Share on LinkedIn

Share on Email

Share More

@VaibhavJ (Believe!! Live your dreams!)     09 September 2014

Excel function - vlookup

Understanding Vlookup.

 

The VLOOKUP function performs a vertical lookup by searching for a value in the left-most column of table_array and returning the value in the same row in the index_number position. Value is the value to search for in the first column of the table_array.

 

Syntax

 

VLOOKUP(lookup_value, table_array, col_index_num, match_type)

 

 

Cell F2 I have used  vlookup function, and formula is: =VLOOKUP(E2, A2:B9,2,0)

 

Here lookup_value is value is the value to search for in the first column of the table_array, in our case it is value of cell E2,

 

table_array  is two or more columns of data that is sorted in ascending order, in our case it is range A2:B9 ,

 

col_index_num is the column number in table_array from which the matching value must be returned. The first column is 1, for 2nd it is 2 & so on, in our case it is 2.

 

match_type is optional, It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, the VLOOKUP function returns an approximate match.

 

 

If index_number is less than 1, the VLOOKUP function will return #VALUE!.

 

If index_number is greater than the number of columns in table_array, the VLOOKUP function will return #REF!.

 

If you specify FALSE for the not_exact_match parameter and no exact match is found, then the VLOOKUP function will return #N/A

 

(Refer attached sheet for explanation)



 10 Replies


(Guest)

"thanks for sharing"

 @VaibhavJ

@VaibhavJ (Believe!! Live your dreams!)     11 September 2014

Dear Readesr, do post if you have any qurry!

 

Thanks..

1 Like
Stranger

Stranger (.)     11 September 2014

yes

Bharadwaj Tallapragada

Bharadwaj Tallapragada (CA-FINAL:NOV2015 ASPIRANT)     12 September 2014

Very useful function bhai, thanks for sharing!
 @VaibhavJ

@VaibhavJ (Believe!! Live your dreams!)     12 September 2014

refer for reverse/backward lookup formula: /forum/reverse-backward-vlookup-advance-excel-function-305491.asp

 

Thanks!!

1 Like
Chintan

Chintan (C.A)     18 October 2014

yes

CA Kumar Mukesh

CA Kumar Mukesh (CA CMA FINAL and Advance Excel Trainer)     28 November 2014

By using vlookup it is necessary for every time that lookup array should be first if I want's to make reference for those cells which before the lookup array in that case I have to either/replace the cell 

IS THERE ANY OTHER WAY DOING SO??????????

 @VaibhavJ

@VaibhavJ (Believe!! Live your dreams!)     28 November 2014

CA Kumar Mukesh

CA Kumar Mukesh (CA CMA FINAL and Advance Excel Trainer)     30 November 2014

Can you explain why we using True or False in Vlookup Function???????????

 @VaibhavJ

@VaibhavJ (Believe!! Live your dreams!)     01 December 2014

Hi

 

when you use false, vlookup will search an exact match. If exact match is not there then vlookup will give you next match nearest to vlookup value..

 

Thanks


Leave a reply

Your are not logged in . Please login to post replies

Click here to Login / Register  


Related Threads


Loading
Start a New Discussion

Popular Discussion


view more »





Subscribe to the latest topics :
Search Forum:

Trending Tags