How to use VLookup Match function in Excel

VLookup Match one of the most powerful and important function in Excel. Sometime we need to use VLookup and Match function together to lookup specific value. If you need better control over during lookup and match the value then you must have to know how to use VLookup Match function in Excel. This dynamic function allows you to reduce the chances of error and improve the capability of VLookup function.

Must Read: Excel VLookup Find First, 2nd Or Nth Match Value in Excel?

During searching any specific value you can see an error when and kind of changes are done in your current workbook. If you delete or add any column in table array then you can get either an incorrect result or an error message. The main reason is that the column number is static and it doesn’t change when any column delete or add.

VLookup function in Excel

Microsoft Excel VLookup function is used to search specific value in the extreme left column of the table_array. This function provide the value in the same row which is based on the index number.

VLookup (lookup_value, table_array, col_index_num, [range])

VLookup function is used to search the value of extreme left column.

Lookup_value: Value to be searched in the extreme first column of the table array.

Table_array: Two or more columns of data. Give cell reference of a range / range name.

Col_index_num: The column index_number in the table from which the searched value must be returned.

Range_lookup: It is an options part of this function. There are two options available in this part.

TRUE: Appropriate Match: This option work properly when first column of your table will be sorted in ascending order only otherwise it displays wrong result.

False: Exact Match: VLookup by default check only on exact match method.

Match Function in Excel

This function is used to returns the relative position of an item in the given array or range of cells that match a specific value in specific order.

MATCH (lookup value, lookup array, [match_type] )

lookup_value: The value that you want to match in lookup_array. The lookup_value argument can be any number, text, logical value or a cell reference to a number, text, or logical value.

lookup_array: It is range of cells being searched.

match_type: It is an optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. By default value for this argument is 1.

Steps to use VLookup Match function in Excel

If you want to use VLookup Match function together in Excel, then you must have to follow given simple steps, have a look.

VLookup Match function in Excel

Must Read: How to use VLookup and Sum function to get total for Multiple columns

Step 1: Create the following structure to understand how to use VLookup Match function in Excel.

Step 2: To search desig, state and sales of any particular employee at once then you have to apply following given VLookup Match function in cell F10

=VLOOKUP($D10,$E$2:$H$7,MATCH(F$9,$E$2:$H$2,0),0).

Step 3: Now, copy cell F10 function in G10 and H10 to get the state and sales amount of lookup employee.

I hope after reading this guide you can easily use VLookup Match function in Excel. If you have any suggestion regarding this guide then please let me know. Thanks to all.

Leave a Reply

Your email address will not be published. Required fields are marked *