# Video Lesson: Lookup / Reference Functions

As listed in the Master Workbook (see link at the top of the page), there are functions grouped into categories. This video is a demonstration of the Experience Level 1 – Lookup & Reference functions.

The functions covered in this video lesson are:

ADDRESS  — COLUMN — COLUMNS — HLOOKUP  — INDEX — INDIRECT

LOOKUP — MATCH — OFFSET  —  ROW  —  ROWS  —  VLOOKUP

Extract of Level 1 – Lookup and Reference Functions from the Master Workbook:

A good follow-up lesson to check out would be either the Advanced Vlookup Examples or Advanced Lookup – Index & Match lessons.

## 33 thoughts on “Video Lesson: Lookup / Reference Functions”

1. Please be more energetic while explaining some important formulae. This looks so dull and lethargic.

• Yo dude, this is not any service and you are having these for free. Feedback and response are fine but cant you be any more grateful?

2. Hi Ben,

This is an excellent resource for learning Excel. Thanks for putting all this together. Very good explanation and the examples also seem very thought of. Good work!

Thanks,
Mumbai, India

3. Why does the match function only work on the “gamelist” but not the other tables? I tried to play around with match using the other tables (MetacriticTable1, etc) and kept getting the N/A error.

4. I am advancing in my career and I am using the lessons here. I appreciate your tutorials. However, will you please tell us first what the importance of each function is, instead of going to how to create the formula? It does not make sense sometimes. It causes frustration

5. Informative for sure. However, you don’t explain what address is, why and how we can use it.

6. I downloaded the master workbook sheet but the examples in this video are different from what is in the master sheet. For example, tab 5 “Pivot Data” has video game data in the video but the spreadsheet I downloaded has pen/pencil/binder/etc sales.

• Thanks for pointing this out! Actually, the video game data was moved to the far right in a spreadsheet called ‘Example Data’. I must have made this video prior to doing that. In the coming weeks, I’ll review the videos and try to make sure everything matches but for now please refer to the Example Data sheet (unnumbered on the far right).

Cheers,

Ben

7. I was wondering if you maybe could post another video on the VLOOKUP, I sometimes get it right and sometimes I don’t and I don’t really know what it is I am doing wrong when it is wrong.

8. How do you set it up so it links to meteoritic tables. I don’t know how to make it refer to that if i am setting it up myself. thanks

9. Thank you so much for providing this resource- this is the best Excel resource I have found online – especially with the workbook. It has helped me so much in my job 🙂

10. Hey Ben, trying to follow the course outline. I have the workbook downloaded. I am currently to follow the lookup & Reference portion, The pivot table you are using seems to be different than any I can find?
can you help?

11. Your tutorials JUST MIGHT be the boost I need to snag a new job I badly want. THANK YOU for providing this resource! ~CG

• Glad you enjoyed the lesson and hope you get the job!!!

Cheers,

Ben

12. The video says there is an issue with playback. Not sure if it is from my end, but I’m not having any trouble playing other videos. Using a PC with Chrome.

13. Fairly new to Excell and LOVE your videos, thank you!! I have several folders in Contacts in Outlook 2010, which the program exports into separate Excell .csv files. I have combined them into 1 file with several sheets. Now I want to add a sheet called RECONCILED that I paste the Undeliverable emails into. Then I want to 1. find and erase all those entries. Then 2. Add email addys and then see if I already have them. Can you help me?

14. Im having the wesdiert problem and do not know how to solve this. Say you have a competition between 3 persons and you want the Vlookup to pinpoint a specifik name that has the highest score to insert it in a cell named and the winner is -> (next cell contains name).. Ive been twisting the =if and =Vlookup but cant figure it out.. It sounds EXTREMLY simple, but for the love of god, I cant solve it..

15. Hey Ben,
Offering some really great stuff here- massively appreciated.
Downloaded the master workbook there a cpl days ago and am trying to follow along with this tutorial video at the mo. Has the pivot table sheet been changed since the making of the vid or am I missing something??

Many thanks.

• Hey Colm,

Good catch, thanks a lot! Fixed it and updated the file.

16. Some feedback – I think its important to talk in more detail about how the INDEX-MATCH combination can be used as a more powerful version of VLOOKUP, including the ability to look up columns to the left of the table array, which is one of the limitations of VLOOKUP. It is vital to anyone using large sheets of data that would otherwise have to be constantly rearranged to get the VLOOKUPs to work. Apart from that, a great video!

• Thanks for the feedback Ollie!

I certainly agree that the INDEX / MATCH (and INDIRECT) combinations are extremely powerful. I’ll make sure to do an advanced lookup video going through some of the more complex usages of the lookup/info related functions.

EDIT: I’ve posted an advanced lookup video on index & match here.

17. Than kyou!