VLOOKUP Explained with 2 Practical Examples

HLOOKUP explained as well

VLOOKUP & HLOOKUP for Dynamic Lookups

These two functions are easy to write, understand, and they’re a major time-saver for simpler lookups.

The VLOOKUP function can search for a specific category or value in a table and return the “opposing” value from adjacent columns.

HLOOKUP works exactly in the same way except that it looks horizontally rather than vertically.

“V” in VLOOKUP refers to vertical and “H” in HLOOKUP refers to horizontal.

Watch 2 Practical Excel VLOOKUP examples below

This video is specially useful if you can’t figure out why your VLOOKUP formula doesn’t work!

Should I use VLOOKUP or HLOOKUP?

If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match,” then use HLOOKUP.

The main limitation is that the value looked with VLOOKUP needs to be in the left-most table column and in HLOOKUP it has to be in the top row of the data set.

Below is an example of the less commonly used HLOOKUP function.

HLOOKUP is used to search along the top row and then look down to retrieve the result from a specified row – the second row in this instance.

The example below uses the VLOOKUP function.

vlookup leila gharani hlookup excel reports

The VLOOKUP formula syntax is the following

vlookup leila gharani hlookup excel reports

  • Lookup_value: A11 or East_01 (in this example).
  • Table_array: the range includes the location of the column lookup value and the columns where the return value is located (i.e. A1:B9).
  • Col_index_num: the number of columns it should move to the right. In this case, the range contains two columns with the match found in the second column.
  • Range_lookup: this argument is important to use correctly if the lookup value is text and non-numeric. If the parameter is left empty, it defaults to “TRUE” meaning that an exact match is unnecessary. But, to work properly, the lookup column must be sorted in ascending order. Otherwise, an error or #NA will appear in the cell if the column isn’t sorted in ascending order and this argument
is left empty. 
In the example above, if FALSE is excluded from the last argument then the formula will return an #NA error, because the lookup column is not sorted in ascending order. (Text shouldn’t include trailing spaces). Use the trim function to “clean up” your text to avoid this from happening.

Building up on the VLOOKUP Function

Wild Cards: Various wild cards can be used with VLOOKUP. You have the option to use the asterisk (*) to match any sequence of characters or the question mark (?) to match any single character. Note that if you want to find an actual question mark or asterisk type a ~ before the character.

Returns the value for the first 7 characters.

Returns the match value for the first “ast” in the name.

Uses both “?” and “*” to search for the first match of a text.

Manipulation: Other operations can be performed on VLOOKUP formula results such as multiplying or dividing two VLOOKUPS with each other.

vlookup leila gharani hlookup excel reports

Nesting: You can also use “nested” formulas with the IF function.

vlookup leila gharani hlookup excel reports

Limitations of VLOOKUP

One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side.

You cannot use the standard VLOOKUP to look at the columns and the rows to find an exact match. The INDEX and MATCH functions are used in this instance and are much more flexible than the classical VLOOKUP.

Tip: Although the CHOOSE function nested in VLOOKUP is a “workaround” tool, the Index and Match combination is more effective to look data up in any direction.

This video shows how HLOOKUP works:

Feel free to Download the Workbook HERE.

Excel Download Practice file

For more information on VLOOKUP, check out this article at Deskbright.

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!



  1. Mohamed ali November 24, 2017 at 2:50 pm - Reply

    Thank you very much it was very easy and clear

  2. Jason December 3, 2017 at 3:54 am - Reply

    index match is way better :D

    • Leila Gharani December 4, 2017 at 7:30 pm - Reply


  3. Rob Lovell December 4, 2017 at 12:25 am - Reply

    Have checked out alot of sites. Yours is the the best explanation that I have found

    • Leila Gharani December 4, 2017 at 7:29 pm - Reply

      Thanks! I’m glad to hear that :)

  4. Syed Azmath March 27, 2018 at 8:30 pm - Reply

    Really appreciate your help

    • Leila Gharani March 29, 2018 at 9:47 am - Reply

      You’re very welcome.

  5. Ricardo April 25, 2018 at 9:21 pm - Reply

    just starting learning excel for a new job. Trying to understand vlookup now need to practice it more. Video was excellent!!

    • Leila Gharani April 26, 2018 at 8:23 pm - Reply

      Thank you Ricardo. Glad you find the video helpful. Good luck for your new job!

  6. zahid May 2, 2018 at 5:36 am - Reply

    very well explained, thanks for the help dear

  7. Mahesh August 18, 2018 at 3:43 pm - Reply

    Hi Leila Gharani,

    Thank you so much for providing Above tutorials.

    Mahesh Goutha from India

    • Leila Gharani August 18, 2018 at 6:35 pm - Reply

      You’re very welcome. Thank you for watching & reading.

      • Hareen Kumar. C September 16, 2018 at 4:15 am - Reply

        Thank u madam, I am learn more from u r tutorials and u r the best teaching, but I had a problem I don’t know fast English understanding knowledge.. but I am listening 2 to 3 times word to word after that I am understood..

  8. Samuel September 13, 2018 at 11:20 pm - Reply

    Thank you 🎼

  9. Victoria September 18, 2018 at 11:44 pm - Reply

    I was able to grasp the concept and how it work as you have provided all the required steps and information! Thank you so much. You are a master!

  10. Walter Szymanski September 25, 2018 at 6:22 pm - Reply

    Leila, your instructions and examples are complete, very well explained and easily understood. Thank you so much for sharing your expertise.

  11. John February 20, 2019 at 9:36 am - Reply

    Hello Leila,

    I am hoping to add a picture of one of my Vlookup tables. It can use any column; not just the left. It annoys me when people regurgitate the same stuff over and over. There are no limitations to Vlookup. The limit is the person who can’t see past the false criticism…There is nothing that INDEX and MATCH can do that VLOOKUP can’t..

    I have tried to add a picture but your site will not allow it.

    If you could email me I would be only too glad to send you the picture.
    My Vlookup table can search any column and any result column. Approximate searches do not have to be ordered either.

    Hope you email me.


    • Bryon Smedley April 7, 2019 at 8:18 pm - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      XelPlus Team

  12. Shanker March 14, 2019 at 3:17 pm - Reply

    HI Leila, It’s very easy and informative way of explanation about lookup with so many option.

  13. Bastien Jean-Baptiste March 27, 2019 at 12:51 pm - Reply

    I teach excel in a university and I found your tutorial very easy to understand and the examples easy to follow. I know that I will simplify lesson thanks to you.

    • Leila Gharani March 28, 2019 at 5:54 pm - Reply

      You’re very welcome Bastien.

  14. Kanchan July 24, 2019 at 9:41 am - Reply

    Leila, you explain everything in the simplest way possible. I really find that helpful always :)

    • Leila Gharani July 25, 2019 at 6:47 am - Reply

      Thank you!

Leave A Comment

Share This