[go: up one dir, main page]
Scribd
Upload
31 views9 pages

Excel Lookup Comparisons

Excel Lookup Comparisons

Uploaded by

aukkaranan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
31 views9 pages

Excel Lookup Comparisons

Excel Lookup Comparisons

Uploaded by

aukkaranan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

Excel Lookup

Comparisons
Financial Analyst Forum

May 7, 2021
Agenda
 Overview of each method, Pros/Cons
 VLOOKUP
 HLOOKUP
 Index Match
 XLOOKUP

 Review in Action
 Discussion: What do you use most?
 Practical uses examples

 Next Steps
VLOOKUP (Vertical Lookup)
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks for a value in the leftmost column of a table, and then returns a value in
the same row from a column you specify.

Lookup value: Value to be found in first column of table


Table array: Table of where data is retrieved, can be range or range name
Col Index Num: the column number in table_array from which the matching
value should be returned. The first column of values in the table is column 1
Range Lookup: A logical value (optional, default to True)
TRUE, 1, or omitted = closest match in first column (ascending order)
FALSE or 0 = Exact match

PROS CONS
Ease of use Left to right only
Awareness/popularity Inserting/moving/deleting may cause issues
Error handling (IFNA, IFERROR)
HLOOKUP (Horizontal Lookup)
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Looks for a value in the top row of a table or array of values and returns the
value in the same column from a row you specify.

Lookup value: Value to be found in top row of table


Table array: Table of where data is retrieved, can be range or range name
Row Index Num: the row number in table_array from which the matching
value should be returned. The first row of values in the table is row 1
Range Lookup: A logical value (optional, default to True)
TRUE, 1, or omitted = closest match in first row (ascending order)
FALSE or 0 = Exact match

PROS CONS
Easy to follow similar logic Up to down only
Inserting/moving/deleting may cause issues
Error handling (IFNA, IFERROR)
INDEX MATCH
INDEX(array, row_num, [column num])

Index returns a value of the cell based on a specified row and/or column number.

Array: Where the data is located, can be range or range name


Row num: The row with the value you want returned
Column num: The column with the value you want returned (optional, if row)

MATCH(lookup_value, lookup_array, [match_type])

Match finds a value and returns the position in a row or column.

Lookup_value: Where the data is located, can be range or range name


Lookup_array: Range of cells or an array containing lookup values
Match_type: 0=Exact, 1=Largest, less or equal, -1=Smallest, greater or equal
INDEX MATCH
INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type])
Combined, the two formulas can look up and return the value of a cell in a table
based on vertical and horizontal criteria.

PROS CONS
Return a value to the left of lookup value Less awareness
Handle H & V structured data More “advanced”
Handle row/columns with insert/delete
Faster
XLOOKUP “modern replacement”
XLOOKUP(lookup_value, table_array, return_array, [if_not_found], [match_mode])

Searches a range or array for a match and returns the corresponding item from a
second range or array.

Lookup value: Value to search for

Table array: The array or range to search

Return array: The array or range to return

If not found: Value returned if no match is found

Match mode: 0 = exact match (Optional, default)


1 = exact or next larger
-1 = exact or next smaller
2 = wildcard match (*, ?, ~)
PROS CONS
Easy to follow similar logic Newer, not as familiar
Error handling built in
Handle insert/move/deleted columns
Lookup Comparison
GROUP DISCUSSION

Q1: Which method do you use most?


Are there certain applications that one method works better than others?

Q2: What challenges/issues have you faced?

_________________________________________________________________

“Once I read up on index match, I never went back to a lookup.” -Paul Harvey

Additional Resources
Excel Jet, https://exceljet.net/
Corporate Finance Institute, https://corporatefinanceinstitute.com/
Trump Excel, https://trumpexcel.com/
Microsoft Support, https://support.microsoft.com
Other helpful sites?
Next Steps

TODAY
Review excel file for practical uses (as time allows)

WEEKS AHEAD
Try using these new methods with your files
(Less sensitive, non-urgent)
See what works well, what doesn’t
Share findings and observations to FAF Teams chat

NEXT MEETING (Aug/Sept)


Group discussion – who has changed?

You might also like