# Excel Video Lesson: Advanced VLOOKUP Examples

The VLOOKUP function is one of the most frequently used functions in all of Excel.  In this video lesson I go over some advanced usages of the VLOOKUP function including:

– In-depth overview of the VLOOKUP syntax:

= VLOOKUP ( lookup_value, table_array, col_index_num, range_lookup ).

– Examples of how to set up intelligent formulas which pull information based on other cells rather than typed in (aka hard-coded) values.

– How to set up an intelligent order query which pulls a full row of information about different orders using one formula that gets copied and pasted.

This entry was posted in Function-Related Lessons and tagged , , , , . Bookmark the permalink.

### 28 Responses to Excel Video Lesson: Advanced VLOOKUP Examples

1. Anonymous says:

Very useful, Ben!
Learned some new methods while reviewing the VLookup function!

2. Alan Ferguson says:

Ben, thanks for your fine instruction. This will definitely broaden my knowledge of Excel.

3. Anonymous says:

Please explain why the value is used in the vlookup that is where i get lost or where I can’t explain

4. Anonymous says:

Hi Ben,

I just finished your video tutorials and I find it very useful. Thanks for sharing! Are you able to help with vlook ups from two different workbooks? My issue is that, I am trying to create a formula that will pull data from a master workbook into my copy. For instance, If I assign something to myself from the master sheet on a particular date, I want the formula to pull that info into my working workbook.

Many thanks

5. Trinny says:

Hi Ben, Enjoying learning from your videos so far, but when I get to the advanced information like VLOOKUP, I’m getting lost. Didn’t see the reasoning for adding an exclamation point or quotes, now I do understand why you are doing it, but I’m not getting the “how” of why certain formulas require different characters or the “when”.

I know it can be confusing to a person like me who really wants to be great at Excel, as I’ve been using it for years, but not at this type of level. I need to learn VLOOKUP and Pivot Tables and have the intuitive sense to use the formulas correct, so my question is, how do you get used to the formulas so that it’s not a foreign language? Even in your reference charts which are very organized and great, there is still the missing elements of when to add additional characters.

Thanks!!

6. Bhaven Vyas says:

It is valuable information and important session
I have still problem of understanding formula like Indirect, data validation and offset function

I will appreciate if you can sent me simplified video of these formula function

Thanks

Bhaven Vyas

7. Natasha says:

I wish I could last more than 5 minutes of this video….I really do. but what is wrong with the voice?! jeez… I do not know if you fake it or not, but it makes it almost impossible to enjoy the video. No offense, but please fix that voice.

• Anonymous says:

his voice is good

8. Kristina says:

Ben,
Okay so is there anything about Excel you DON’T know? Come fix my horrible spreadsheets at work that I was just given responsibility of! haha.
Thank you for the videos, hopefully I can adapt them and make my life easier!! 🙂

• EDWARD says:

9. jos says:

Hello, I still cannot understand the double quote + apostrophe + double quote thing.

10. P. N. Rao says:

Boon for beginners!
Thanks a lot

11. koolx says:

the video wont play.. please fix this

12. Tom says:

Quick question regarding the final example. I did the example and when I copied it through I ended up getting dates rather than units in the Units, Cost and Total columns. Upon examining the formatting, I found that the Vlookup cells had been formatted as Date cells rather than number cells. Is there a way to drag and copy the Vlookup formula so it keeps the format value from the pulled column (ie column 7’s number value)?

[Fill without formatting doesn’t seem to work?]

• Anonymous says:

Any chance you got your reply? I’ve been having the same problem too.

13. Glenn says:

Awesome video – thank you for the explanation. I have been searching for days for what I think should be a simple VLOOKUP question and still no answers on any board anywhere. I have a spreadsheet that track tasks on different sheets – if a task is complete the user checks complete, it greys out and the value for the row turns to True. I have a summary sheet that reads all the individual sheets and displays the False tasks – the ones that are still open. The only problem is if the Task is True the VLOOKUP reads the next column and displays it and then the next row the same entry – logic of the VLOOKUP – write the first FALSE answer you find. Now my questions is can I use Nested IFs or some other logic to identify the TRUE and skip that row and write the next entry without writing duplicates.

14. charles says:

Hi
I am a teacher and need to link my class list on sheet 1 which is sorted alphabetically showing all learners to a sheet 2 which is my attendance register where the males and females need to be separate but also still in alphabetical order. Is there a formula I can use to link the names and surnames from the class list to the register.

15. Anonymous says:

Hi Ben,

I find this advanced VLookup examples really useful! I can reapply it directly to my work. Thank you so much! I will definitely go through all the video lessons you have posted here. It will definitely help sharpen and improve my excel skills. Thank you for your effort! 🙂

Best regards,
Hui

• hami says:

hi
dear friend
I need a Advanced VLOOKUP Examples workbook (templates ), can you help me? can you send this file in my email:
hami_6227@yahoo.com
Thanks a lot

16. Bhimraj says:

vlookup fomrulas

17. Dee Gibson says:

I have data that require more than one lookup value, should I use vlookup of an “if” statement. I need to have three items match in order to return the right anwser

18. Frank says:

Mate, sitting here in Melbourne, Australia brushing up on some very rusty Excel skills… your lessons the way you walk through and explain each aspect of the functions & why are simply brilliant.
Just a big thank you for making this available to us all.

19. chip says:

“!\$A\$4:\$E\$20”

Ben,
What is the exclamation point for before the range?

• Hey Chip,

Whenever you reference a sheet that isn’t your current sheet, it inserts an exclamation point between the sheet name and the range. For example, open up a blank workbook, and in cell A1 type = and then reference it to A1 on Sheet2 by clicking on it.

You’ll see that it automatically inserts the exclamation point before you even select a cell, and this is the formula that results:

=Sheet2!A1

So in the example, you need to insert an exclamation point before the reference or it will not work.

Hope that clears it up,

Ben

20. Jo says:

Hello Ben

I have just been viewing the lookup video and practising with your worksheet. I have found this extremely useful. The depth of content and pace etc I believe to be perfect, especially for me with my limited knowledge. However I do get into a lot of trouble when working with formulas not knowinf how to handle ‘0’ and blanks. At the monent It is luck whether I get a 0, a blank, #N/A or Jan 00 if working with dates. Can you help with this?

• Hi there,

VLOOKUP will return a 0 for blank values that it finds when looking up an item, and it will return #N/A if it doesn’t find the item in the table at all. If you have the result cell formatted as a date, it will show Jan, 1900 because Excel stores dates as the number of days since Jan-0-1900. For example today, 12/27/2012 shows up as 41270 when formatted as general (41,270 days from Jan-0-1900).

To get around this issue, you can put the VLOOKUP formula inside an IF statement like so:

=IF(VLOOKUP()=0,””,VLOOKUP())

This instructs Excel to check IF the VLOOKUP will return a 0, and if so to return a blank (which is done using two quotation marks with no space “”). IF it does not equal 0, then give me the VLOOKUP result. Replace VLOOKUP() with your VLOOKUP statement to try it out.

You would only want to do this if you don’t have items that SHOULD be returning a 0.

Here are a couple related video lessons:

Experience Level 1 – Logical Functions (e.g. IF)
http://excelexposure.com/2012/03/04/level-1-logical-functions-and-formulas/

Experience Level 1 – Date & Time Functions (e.g. TODAY)
http://excelexposure.com/2011/09/27/level-1-date-and-time-functions/

Cheers,

Ben

• Ratna kishore says:

Thank you so much this is so useful to me..

• Paul says:

you can also use the iferror function to get around those pesky #N/A results, if you know you want to replace the error with a certain result ie “0”,”-” or even “”.

syntax is as follows.
iferror(value, value_if_error)
and an example.
=IFERROR(VLOOKUP(B88,L88:Q104,2,false),”-“)