Guide for Understanding Lookup Functions and

Table Relationships in Excel

=INDEX(C3:F7,XMATCH(H3,B3:B7,0),XMATCH(I2,C2:F2,0))

Paul Barnhurst
#TheFPandAGuy
Contact: [email protected]
Website: TheFPandAGuy.com
LinkedIn: Paul Barnhurst
 EXCEL LOOKUP FUNCTIONS
Excel Lookup Functions Include:

1. Vlookup — Used to return a lookup value from a column in a vertical table/array of data.
2. Hlookup — Used to return a lookup value from a row in a horizontal table/array of data.
3. Index/Match/Match* — Used for two-way lookups and returns a row/column position from a table/array of
data.
4. Offset/Match/Match* — Another way of doing a two-way lookup and returns a row and column position based
on the starting cell reference.
5. Lookup — Used to look in a single row or column and return a value from the same position in a second row or
column. Compatible with all versions of Excel
6. DGet — Excel database function used to return a record based on a set of criteria.
7. Xlookup — A relatively new array formula that allows performing a one-way or two-way lookup in a horizontal
or vertical fashion. The formula is only available in Office 365.
*Note: Match can be replaced with the new XMatch function which is only available in Office 365.

Many an Excel argument has happened over which formula is best for conducting a lookup. The reality is the for-
mula that is best is the one that gets the job done and is understood by the people using the spreadsheet. All
functions have pros and cons. Do what works best for you.
 EXCEL Non-Function Lookup Methods
In addition to the traditional function methods Excel has several non-function methods that can be used for cre-
ating relationship, linking and looking up values between different datasets:

1. Tables—In Excel one can create two or more unique tables using the Excel table functionality and then relate the
tables together via the data model relationships functionality. Relating the tables together makes all values be-
tween the tables available.
2. Power Query— One can lookup data from any number of tables using the Power Query Merge function. For
those familiar with SQL the merge function works like the Join function in SQL which is used to join tables togeth-
er. The different type of Merges available in Power Query Include:
• Left Outer Join (all rows from first table, matching from second table)
• Right Outer Join (all rows from second table, matching from first table)
• Full Outer Join (all rows from both tables)
• Inner Join (only matching rows from both tables)
• Left Anti (rows only in first table)
• Right Anti (rows only in second table)
3. Power Pivot Data Model—In Power Pivot Tables are related together via data model. This is the same basic meth-
od number one and the only difference is the tables are related together in the PowerPivot data model vs Excel.
Learning to do this makes available the DAX formula language and is a great place to start learning about Power BI
and data models which require building relationships between tables.

Both function and non-function methods for conducting lookups and relating data sets together have pros and
cons and learning all the methods allows one to select the best method for the job at hand.
 EXCEL LOOKUP FUNCTIONS
1. Vlookup — used for conducting a vertical lookup in Excel.
Syntax: =Vlookup( lookup_value, table_array,Col_Index_Num, [range_lookup])
• Lookup_Value—This is the lookup value, in below example this is the Student Id that we are looking up.
• Table_Array—The area containing the values that are being looked up. In the example below it’s the table with Student ID
and Full Name columns.
• Index_Num—This is the column number that contains our values. This is column 2 and this column will return the full
name.
• Range_lookup—This is optional as signified by the brackets in the syntax. Used to return an exact match or approximate
match. Exact Match is false and approximate match is true, defaults to approximate match if no value entered.

2. Hlookup— Used for conducting a horizontal lookup in Excel. The syntax and logic is the same for a horizontal lookup (Hlookup) the
only difference is the table/array the data being pulled from is in a horizontal format instead of vertical format.
 EXCEL LOOKUP FUNCTIONS
3. Index-Match-Match or Index-XMatch*-XMatch* — used for conducting a two-way lookup in Excel.
Syntax: = Index (array, row_num, [Col_num])
Syntax: = Match (lookup_value, Lookup_array,[match_type]
Syntax: = XMatch (Lookup_value, Lookup_array,[match_mode],[Search_Mode]
Index—The first argument in an index formula is the array/table that contains the data being searched. Below is a two-way table
with city, product and corresponding sales for each city and product. The array used in the index formula for the below example
would be C3:F7 and would include only the data and not the table headers in the array.

The two match arguments are the way to identify the row and column position that contains the data being returned.
• 1st Match: The first match identifies the row position. In the above example to return Los Angeles the Match function
would look like = Match(“Los Angeles”,B3:B7,0) and it would return the number 3 for the 3rd row in the array.
• 2nd Match: The second match identifies the column position. In the above example to return Drinks my match function
would look like = Match(“Drinks”,C2:F2,0) and it would return the number 4 for the 4th column in the array .
Sample formulas, 1st for Match, 2nd for XMatch
=INDEX(C3:F7,MATCH(“Los Angeles”,B3:B7,0),MATCH(“Drinks”,C2:F2,0)), Result = 140
=INDEX(C3:F7,XMATCH(“Los Angeles”,B3:B7,0),XMATCH(“Drinks”,C2:F2,0)), Result = 140
* XMatch performs the same basic function as match but is a more versatile and powerful function that was designed as potential
replacement for Match and includes more options in the formula such as match_mode and search_mode.
 EXCEL LOOKUP FUNCTIONS
4. Offset-Match-Match or Offset-Xmatch-XMatch — used for conducting a two-way lookup in Excel.
Syntax: = Offset (reference, rows, cols, [Height],[Width])
Syntax: = Match (lookup_value, Lookup_array,[match_type]
Syntax: = XMatch (Lookup_value, Lookup_array,[match_mode],[Search_Mode]
Offset: The main difference between index and offset is Index references the whole table and then uses row and column positions to
return the data. Where Offset references a set location and one tells it how many rows and columns to move in order to find the de-
sired value. In the below table the offset reference point would start with cell B2 and we would then move down 2 rows and over 1
column in order to return the value for Bread sold in the city of New York which is 168.

The match functions are used to identify the number of row and column positions the offset should move from the starting position.
• 1st Match: The first match identifies the row position. In the above example to return New York the Match function would
look like = Match(“New York”,B3:B7,0) and it would return number 2 and offset would move down two rows from B2 to B4.
• 2nd Match: The second match identifies the column position. In the above example to return Bread the match function
would look like = Match(“Bread”,C2:F2,0) and it would return the number 1 and offset would move over one column from
B2 to C2.
Sample formulas, 1st for Match, 2nd for XMatch.
=OFFSET(B2,MATCH(H3,B3:B7,),MATCH(I2,C2:F2,0)), Result = 168
=OFFSET(B2,XMATCH(H3,B3:B7,),XMATCH(I2,C2:F2,0)), Result = 168
 EXCEL LOOKUP FUNCTIONS
5. Lookup — Looks in a single row or column and returns a value from the same position in a second row or column. This formula
is compatible with all versions of Excel and is the original Excel Lookup formula
Vector Form Syntax: =LOOKUP(lookup_Value, lookup_Vector,[result_vector])
• The lookup value the formula searches for in the vector.
• The lookup vector is a range that contains only one row or column.
• This result vector is optional and contains only one row or column.
In the below example cell B7 is the lookup value, cells A2:A5 is the lookup vector and cells C2:C5 is the result vector. Returns $200
the price of pants.

Array Form Syntax: = Lookup (lookup_Value, array)

• The lookup value the formula searches for in the vector.
• The array contains the range being compared and returns a record matching the lookup value.

In the above example the function is looking up the scores and returning the corresponding ranking for each score. The score of
10 in the above example is a failing score so it returns fail.
 EXCEL LOOKUP FUNCTIONS
6. DGET — This is an Excel database function used to return a single value from a field within a database record that matches cer-
tain criteria.
Syntax: = DGET(Database, Field, Criteria)
• Database — This is the table of records from the excel file. In below example this is B5:310.
• Field — This is the field being returned based on the criteria set. In below example this is “Cost”.
• Criteria — This is the range of cells that contains the conditions specified in the search criteria. B2:E3 in below example.

In the above example cell H15 contains the formula that is looking up the “cost” based on the criteria in cells B2:E3. In this case
the criteria for lookup incudes a red, large, blouse. The database is cells B5:E10 and the result that the formula returns based on
the criteria entered above is $15.00.
 EXCEL LOOKUP FUNCTIONS
7. Xlookup — Xlookup is a new lookup function in Excel and available in Office 365. It is designed to perform the functions of
vlookup, xlookup, and Index-Match-Match all in the same function. The below is one of many examples of how to use xlookup for
more examples of how to use xlookup review my blog at excelreviews.net
Syntax: = Xlookup(lookup_value, lookup_array,[if_not_found],[match_mode],[search-mode])
• Lookup_Value — The value being looked up. In below example this is Salt Lake, Cell G3
• Lookup_Array — The array that contains the lookup values we are searching. Below this is E3:E7.
• Return_Array — The array that contains the value to be returned. Below this is A3:a7.
• [If_Not_Found] — This argument is optional and is used when no result is found. Below example is “Salt Lake”
• [Match_Mode] — This is the match mode and has four options including exact, smaller, larger, and wildcard.
• [Search_Mode] — This determines the search order and options include starting at first, last or a binary search.

In the above example we are looking up Salt Lake as the lookup value in cell H3 and the lookup array is cells E3:E7 (List of cities) and
the return array is A3:A7 (Revenue) and then we have added an optional argument to return “Not Found” if the city in the lookup
value does not exist. In the above example the formula returns the answer of Not Found as Salt Lake is not in the list of cities that
we have revenue for and therefore cannot be found.
 EXCEL Non-Function Lookup Methods
1. Table Method — This method instead of using a lookup formula creates a relationship between tables using a foreign and primary
column (key). This method works very similar to how a database works when creating relationships between tables. In order to
create a relationship click on the data ribbon and select the relationship icon, highlighted by the red box below.

The Create Relationship dialog box will be displayed. In the below example we are linking the Student ID (Foreign Key) from the
Full_Name table to the Student ID (Primary Key) of the Student_ID table. We do this by selecting the tables and the columns that are
to be related to each other from each table.

We then insert a PivotTable and when the PivotTable screen comes select the click box Add this data to the Data Model. This makes
it possible to view both tables using a PivotTable. See next page for an example of what this looks like.
 EXCEL Non-Function Lookup Methods

In the above image we have inserted a PivotTable with student_ID and Cumulative GPA. In the above picture you will no-
tice that we are showing two tables in the PivotTable screen, the table called full_Name 1 and the table called Student_ID
1. Both of these tables have been loaded to the data model and a relationship has been created between the tables so the
columns from both tables are available in the PivotTable.
 EXCEL Non-Function Lookup Methods
2. Power Query Merge Columns — This method involves connecting tables in Power Query via the merge queries functionality and
makes it possible to connect any and all columns between the tables.
• After creating the tables in Excel load the tables to Power Query.
• To load a table highlight the desired table for loading and go to Data on the ribbon menu and select the From Sheet icon.

• Click From Sheet and it will load the sheet and bring up the power query window. Repeat this process to load all necessary
tables into Power Query, in this example we have two tables Student_ID and Full_Name. Once the tables are loaded in
Power Query select the Student_Id table and select the Merge Queries function and click on merge queries.
 EXCEL Non-Function Lookup Methods
• After selecting Merge Queries the merge query screen will come up as shown below. Select the column from each table that you
want to use to join the data together. In the below example we are joining the tables using the Student ID column in each table.
When you select a column it will highlight the column green and you can use as many columns as necessary to create a unique ID
between the columns.

• Once you select ok you will see your table in Power Query with a new column called full name and it will say table. This table will
then become available to select by clicking on the double arrow icon on the full name column and selecting the columns you
want to add to the student ID table. You can add one or all columns in the full name table to your student Id table. One of the
benefits of this approach is you can link all columns together without having to create multiple lookup formulas to add each col-
umn one at a time.
 EXCEL Non-Function Lookup Methods
3. Power Pivot — This method involves taking the tables you have loaded to Power Query and loading them to the Power Pivot Data
Model and then creating a relationship between the tables.
• You can load your tables to Power Pivot by right clicking on the query (Student_ID )in the queries and connections pane
and selecting load to and then clicking on add to the data model when the Import Data screen comes up.

• After adding the tables to the Data Model you will click on Power Pivot on the ribbon and will see the below menu. From
the menu you will click on Manage and this will open Power Pivot and you will be able to create a relationship between the
tables.
 EXCEL Non-Function Lookup Methods
• After clicking on Manage the screen to the right will be
brought up and you will be able to see both of your tables and
it will have a tab on the bottom to click on either table and
see all related columns.
• At this point you will click on Diagram view this will bring up
the below screen showing each table as a diagram.
• The Diagram View box as displayed below will come up and
you will right click on the column you want to relate. In the
below example you would click on Student ID and this will
bring up a menu that contains as the first option Create Rela-
tionship and you will click on this option.
• The Create Relationship dialog box will appear and you will
select the tables and columns that you want to relate and click
ok.
 EXCEL Non-Function Lookup Methods
• In Power Pivot after creating the relationships between the tables the diagram view will show the tables with a line between
the tables that have a relationship. This is where your data model will be built and makes available reporting that is not easily
doable in Excel without Power Pivot.
 This guide was brought to you
by Paul Barnhurst (aka TheFPandAGuy)
For more information please go to Paul’s website Excel Re-
views. Over the coming weeks posts will be provided to find
some of the best resources on the web for learning each func-
tion and method mentioned in this guide including:
1. Vlookup
2. Hlookup
3. Index-Match-Match
4. Offset-Match-Match
Paul Barnhurst 5. Lookup
#TheFPandAGuy 6. DGet
Contact: [email protected] 7. Xlookup

Website: TheFPandAGuy.com 8. Table Relationships in Excel

LinkedIn: Paul Barnhurst 9. Power Query

10.PowerPivot Table Relationships
Instagram: theFPanAaguy
Twitter: @excel_reviews