We use cookies to give you the best experience on our website. If you continue without changing your settings, we’ll assume that you are happy to receive all cookies on this website. However, you can change your cookie setting at any time. x
Home > Blog

Advanced VLOOKUP: Nested lookups

Sometimes it can be useful to nest lookups.  One use of this is to call on different data sets depending on what you’re looking up.  A trivial example is shown below: 

Example of using Nested VLOOKUP

Here we want to be able to select the student's name and then the subject and display the grade.  The data set we have is a table of students, subjects and scores (not grades).  Obviously, this could be done with a separate table and a filter however for the purposes of this blog this is a nice example. 

Here the formula in B6 is 

=VLOOKUP(VLOOKUP(B2,E2:H7,VLOOKUP(B4,N2:O4,2,FALSE),FALSE),K3:L8,2,TRUE) 

Dissecting this formula gives: 

VLOOKUP(B2,E2:H7,VLOOKUP(B4,N2:O4,2,FALSE),FALSE) = the raw score for Mark in Science 

VLOOKUP(B4,N2:O4,2,FALSE) = Look up in the N2:O4 list for how many columns to count over for Science in the scores section E:H 

In normal language:  

Lookup Score in the Grades K:L section and find the exact match or lowest closest number 

Score = Lookup B2(Mark) in the Raw Score date and count (lookup in the N:O data for how many columns to count over for B4 (Science) and only find exact match 

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.