Select a blank cell, copy the below formula into it and press the Enter key to get the result.Īs the below screenshot shown, you want to search for an order ID number “10006”, if the number can’t be found, then do the approximate match.
HOW TO USE VLOOKUP IN EXCEL 2003 HOW TO
Example 1: Exact match VS approximate match with VLOOKUP functionĪs the below screenshot shown, how to search for an exact match value “Peach” and return its corresponding value from the third column in the table range B3:E8? You can do as follows to achieve it.ġ. This section provides examples to show you how to use the VOOKUP function in Excel. It will return the #N/A error value if the look up value can’t be found. The VLOOKUP function will use approximate match by default.ģ. They are exact match and approximate match. There are two matching modes for VLOOKUP function.
The lookup value is in the left-most column, and the result value should be in any column to the right of the look up value.Ģ. The LOOKUP function only looks for value from left to right. The VLOOKUP function will return the matched value from a table based on the lookup value in the first column.ġ. In this circumstance, the values in the first column of the table_array don’t need to be sorted. =VLOOKUP(lookup_value, table_array, col_index, 0) =VLOOKUP(lookup_value, table_array, col_index, FALSE) But if there are two or more values in the first column of table_array that match the lookup_value, the vlookup will only use the value of the first found. If an exact match value can’t be found, it will return a #N/A error value. If the range_lookup is either FALSE or is 0, the VLOOKUP function will only return an exact match.Otherwise, VLOOKUP might not return the correct value.
Important: In this circumstance, the values in the first column of the table_array must be sorted in ascending order =VLOOKUP(lookup_value, table_array, col_index) =VLOOKUP(lookup_value, table_array, col_index, TRUE) If an exact match can’t be found, it will match the next smallest value. If the range_lookup is either TRUE or is omitted, the VLOOKUP function will return an exact match or an approximate match.Range_lookup ( optional): This is a logical value that determines whether this VLOOKUP function will return an exact match or an approximate match: The first column number is 1 for the left-most column of table_array.Table_array ( required): The table contains two or more columns where the lookup value column and the result value column locating.Ĭol_index ( required): The specific column number (it is an integer) of the table_array, which you will return the matched value from. It must be in the first column of the table_array range. Lookup_value ( required): The value you are searching for.
0 kommentar(er)
