# 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.

Download the **Advanced VLOOKUP Examples workbook** to follow along with the video.

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!! :)

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

Boon for beginners!

Thanks a lot

the video wont play.. please fix this

Very helpful write up!

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?]

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.

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.

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

vlookup fomrulas

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

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.

“!$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

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

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

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),”-“)