What You'll Learn / Key Take-Aways

- How VLOOKUP and HLOOKUP work and when to use each one.

- The four main components of a lookup formula and typical syntax.

- Two critical rules: lookup value position and first-match behavior.

- How to create a unique key with concatenation when duplicates exist.

- Common pitfalls and practical tips to make lookups robust in real reports.

## Why use lookups

VLOOKUP and HLOOKUP are simple, built-in Excel functions designed to pull a value from one table into another when both tables share a common field. Typical use cases include:

- Merging product price data into a sales table by product ID.

- Pulling employee information into a transaction log based on staff ID.

- Matching reference values to a main dataset so you can run calculations or create reports.

If you have two arrays and a shared field, a lookup is often the fastest way to bring everything together.

## VLOOKUP and HLOOKUP explained

VLOOKUP and HLOOKUP share the same concept and very similar syntax. They differ only in orientation.

VLOOKUP

- Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

- lookup_value: the value you want to find in the table array. This is typically the common field between the two tables.

- table_array: the table where Excel will search for the lookup value. For VLOOKUP this table must have the lookup column as the leftmost column.

- col_index_num: the column index in the table_array from which to return a value. The first column in the table array is 1.

- range_lookup: optional. Use FALSE or 0 for exact match. Use TRUE or 1 for an approximate match. Most of the time you should use FALSE.

Example: =VLOOKUP(A2, $G$1:$H$5, 2, FALSE)

In plain terms, this searches column G for the value in A2. When it finds a match it returns the value two columns over from the left edge of the table array.

HLOOKUP

- Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

- HLOOKUP works the same way but operates horizontally. The lookup row must be the top row of the table_array. The function then returns a value from a specified row beneath it.

Use HLOOKUP when your reference table is transposed, meaning headers run across a top row instead of down a left column.

## Two rules you must remember

1) The lookup column must be the first column in the table array for VLOOKUP. For HLOOKUP the lookup row must be the first row. If your lookup field is not in the first column or first row you will get incorrect results or errors.

2) VLOOKUP and HLOOKUP return the first match they find. If the lookup value appears multiple times, Excel stops at the topmost instance (VLOOKUP) or leftmost instance (HLOOKUP) and returns that result. If you need the correct value for each row, you must ensure your join key is unique.

## Making keys unique with concatenation

Many real datasets are not unique on a single field. For example, you might have player stats by year, and player names repeat across years. The solution is to create a composite key that combines multiple fields into a single unique identifier.

- Concatenation in Excel: use the ampersand operator. For example, =A2 & "|" & B2 will join the value in A2 and B2 with a pipe separator.

- Use TEXT or VALUE if you combine numbers and text and need consistent formatting. For example: =A2 & "|" & TEXT(B2, "0000")

Make the same composite key in both tables and use that key as the lookup_value. This prevents the first-match problem and ensures a correct row-level join.

## Practical example: merging product table

1. Identify the common field between the two tables. If a single field is unique, you can use it directly. If not, create a composite key with concatenation.

2. Create a new column in the destination table for the value you want to bring in, for example Price.

3. Type the VLOOKUP formula using absolute references for the table_array so you can copy the formula down:

- Example: =VLOOKUP(A2, $G$2:$H$100, 2, FALSE)

4. Press Enter and then copy the formula down the column.

5. If you see #N/A, check for mismatched data types, trailing spaces, or a misaligned table range.

## Common errors and how to fix them

- #N/A: Lookup value not found. Check spelling, stray spaces, or data types. Use TRIM to remove extra spaces.

- Incorrect result even though value exists: you may be using approximate match. Set range_lookup to FALSE.

- Wrong column returned: Verify col_index_num is counting from the leftmost column of the table_array.

- Multiple matches returning the wrong row: ensure a unique key exists for each row.

## Alternatives to VLOOKUP/HLOOKUP

- INDEX and MATCH: More flexible when the lookup column is not the first column. Use MATCH to find the position and INDEX to return the value.

- XLOOKUP: Newer Excel versions include XLOOKUP which handles both vertical and horizontal lookups, supports leftward lookups, and defaults to exact match.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Lock your table range with absolute references ($) before copying the formula.

- Always use FALSE (exact match) for text lookups unless you specifically need an approximate match.

- Normalize and clean data before looking up: use TRIM, CLEAN, and consistent number formatting.

- Avoid merged cells in lookup ranges. They break table logic.

- If you have many lookups and performance becomes an issue, consider Power Query or creating a single joined table outside of formulas.

## FAQ

Q: What is the difference between VLOOKUP and HLOOKUP?

A: VLOOKUP searches a column and returns a value from a column to the right. HLOOKUP searches a row and returns a value from a row beneath it. Use HLOOKUP only when your reference table is transposed.

Q: Why does VLOOKUP return the wrong value when duplicates exist?

A: VLOOKUP returns the first match it finds. If the lookup value appears multiple times you must create a unique key or use a different approach so the function can return the intended row.

Q: Should I use TRUE or FALSE for the range_lookup argument?

A: Most of the time use FALSE for an exact match. TRUE is appropriate when you need an approximate match, such as mapping scores to grade brackets, and the lookup column is sorted.

Q: How do I look up a value to the left of my lookup column?

A: VLOOKUP cannot look left. Use INDEX and MATCH, or use XLOOKUP if your Excel version supports it, because both allow leftward lookups.

Q: How do I handle trailing spaces or mismatched data types?

A: Use TRIM to remove trailing spaces. Convert text numbers to real numbers with VALUE, or format numbers to text consistently with TEXT when building composite keys.

## Summary of Key Points / Take-Home Messages

- VLOOKUP and HLOOKUP are basic but powerful tools for merging tables when you have a shared field.

- Use exact match (FALSE) almost always, and make sure the lookup field is the first column or first row of your table array.

- If duplicates exist, build a unique composite key using concatenation and use that as the lookup value.

- Consider INDEX/MATCH or XLOOKUP for more flexible or modern lookups. Clean and lock your ranges to avoid common errors.

With these rules and practices you can build reliable, repeatable lookups and merge data confidently in Excel.