Skip to main content

How to Use VLOOKUP or HLOOKUP to find an exact match

SUMMARY

The VLOOKUP and HLOOKUP functions contain an argument called range_lookup that allows you to find an exact match to your lookup value without sorting the lookup table.

Note It is not necessary to sort the lookup table if you use the range_lookup argument correctly.


MORE INFORMATION

The syntax of these functions are defined as follows.


VLOOKUP Function

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)


where:

Argument Definition of argument
---------------------------------------------------------------------

lookup_value The value to be found in the first column of the array.

table_array The table of information in which data is looked up.

col_index The column number in the table_array for which the
matching value should be returned.

range_lookup It is a logical value that specifies whether
you want to find an exact match or an approximate match.
If TRUE or omitted, an approximate match is returned; in
other words, if an exact match is not found, the next
largest value that is less than the lookup_value is
returned. If FALSE, VLOOKUP finds an exact match. If an
exact match is not found, the #N/A error value is returned.


Note If range_lookup is TRUE or omitted (for an approximate match), the values in the first column of table_array must be sorted in ascending order. If range_lookup is FALSE (for an exact match), the table_array does not need to be sorted.
Example That Uses FALSE as the Range_lookup Argument
The following list contains some fruits and their respective colors. Notice that the first column is not sorted:



A1: Fruit

B1: Color

A2: Kiwi

B2: Green

A3: Grape

B3: Yellow

A4: Banana

B4: Red

A5: Apple

B5: Pink



The following formula finds the color (Red) that corresponds to the fruit Apple. You can type the formula in any cell on the worksheet:

=VLOOKUP("Apple",A2:B5,2,FALSE)


Notice that if you change the range_lookup argument to TRUE, Excel returns the #N/A error, because the first column is not sorted.


HLOOKUP Function

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)


where:

Argument Definition of argument
---------------------------------------------------------------------

lookup_value The value to be found in the first column of the array.

table_array The table of information in which data is looked up.

row_index The row number in the table_array for which the
matching value should be returned.

range_lookup It is a logical value that specifies whether
you want to find an exact match or an approximate match.
If TRUE or omitted, an approximate match is returned; in
other words, if an exact match is not found, the next
largest value that is less than the lookup_value is
returned. If FALSE, VLOOKUP finds an exact match.
If an exact match is not found, the #N/A error value is
returned.


Note If range_lookup is TRUE or omitted (for an approximate match), the values in the first row of table_array must be sorted in ascending order. If range_lookup is FALSE (for an exact match), the table_array does not need to be sorted.
Example That Uses FALSE as the Range_lookup Argument
The following list contains some fruits and their respective colors. Notice that the first column is not sorted:

A1: Fruit

B1: Color

A2: Kiwi

B2: Green

A3: Grape

B3: Yellow

A4: Banana

B4: Red

A5: Apple

B5: Pink



The following formula finds the Color column, and returns the third item (-1) for the heading Yellow. You can type the formula in any cell on the worksheet:

=HLOOKUP("Color",A1:B5,3,FALSE)


Notice that if you change the range_lookup argument to TRUE, Excel returns the #N/A error, because the first column is not sorted.

Comments

Popular posts from this blog

The sign-in method you're trying to use isn't allowed. For more info, contact your network administrator

Above problem occur when you have not added a group (of which User try to login)  in your local policy login. To resolve the issue, follow the steps,
Log in as the administrator on the server, then start the Group Policy Management Editor by running the  gpmc.msc command from PowerShell or the Command Line window. 1. In the Group Policy Management window on the left hand side, select Group Policy Management. 2. Click to expand the  Forest tree node.
3. Click Domains.
4. Select your domain name.
5. Click Group Policy Objects. 6. In the right-hand window, double-click Default Domain Controllers Policy.
7. Right-click Default Domain Controllers Policy and Select Edit.
8. In the Group Policy Management Editor window, click Default Domain Controllers Policy.
9. Click Computer Configuration, and then click Policies.
10. Click Windows Settings, and then click Security Settings. 11. Click Local Policies and then click User Rights Assignments.
12. In the right pane, click Allow log on locally. 12. Cli…

ORA-38104: Columns referenced in the ON Clause cannot be updated

In my case, this error pertains to Oracle Merge query. After 30 minutes of time wastage and a lot of hunting on different forums, I come to know that where and why was my merge query throwing exception.

I was giving multiple criteria in ON clause of merge query, and was also trying to update a column which I have mentioned in ON clause. Once I remove that column from update clause of merge query, it executed successfully.

I hope that this will also works for you.

In case if this post help you, then please comment on it.

Thanks.

Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)

Yesterday, I start SQL Server 2005 and try to connect with sa user but was enable to connect with the 'sa' user and got this error:

Cannot use the special principal 'sa' (Microsoft SQL Server, Error: 15405)After doing some google, I found out the solution which I am sharing so that others can get all the possible solutions from this article.
The titled message mean that your sa user is disabled. To enable it, you have to follow below mentioned steps:1) you have to change the authentication mode: go to Management Studio and open Object Explorer. Connect to your server and right-click on the server name, then select Properties. Go to Security tab and under Server Authentication select "SQL Server and Windows Authentication Mode".2) Also, after you follow the directions above, you will also need to explicitly enable the sa account as well. Go to Security->Logins. Right-click on the "sa" account, select "Properties". In the list on the lef…