Key Take-Aways

- When you need to match rows that are not unique by name alone, create a unique key by concatenating fields such as player and year.

- VLOOKUP requires the lookup key to be in the first column of the lookup array and often uses exact match (0 or FALSE).

- Use absolute references to lock the lookup table when copying formulas and partial absolute references to keep the lookup value column fixed when copying across.

- Change only the column index number when copying a VLOOKUP across columns to pull different fields from the same lookup table.

## Why a unique key matters

VLOOKUP searches the leftmost column of the table array for the first match. If you look up only by player name and a player appears in multiple years, VLOOKUP will return the first match and ignore later ones. To correctly match player-by-year rows you must create a unique key that represents the exact row you want to match.

Definition: Concatenate - combining two or more text values into one text string. In Excel you can use the ampersand operator like `B2&C2` or the `CONCATENATE` / `CONCAT` functions.

## Step-by-step walkthrough

1. Create the key column in both tables

- Insert a new column A in both the master sheet (hitting data) and the lookup table (fielding data).

- In the first data row of each sheet, build the key with the player and year. Example formula in A2: `=B2&C2` where B is Player and C is Year.

- Fill the key down the column so every row has a unique combined value like "Smith2012".

Why this works: now each row has a one-to-one identifier that both tables share, so lookups will match the exact row.

2. Prepare the target columns

- In your master sheet (where you want to pull fielding metrics into hitting data), add placeholder headers for the fields to import: Position, Put Outs, Assists, Errors, Double Plays.

- These columns will hold the VLOOKUP formulas.

3. Write the VLOOKUP for the first field

- Example formula for Position in P2:

`=VLOOKUP(A2, 'Fielding Data'!$A$1:$H$12056, 4, 0)`

Breakdown:

- `A2` is the lookup value, the concatenated key in the master sheet.

- `'Fielding Data'!$A$1:$H$12056` is the table array where the lookup happens.

- `4` is the column index number relative to the table array where Position lives.

- `0` requests an exact match. You can also use `FALSE` instead of 0.

- Press Enter and confirm the correct value appears for the first row.

4. Lock the table array with absolute references

- Before copying the formula down, lock the table array so it does not move. Press F4 when your cursor is on the table reference to change it to absolute: `'$A$1:$H$12056'`.

- This pins the range so copies of the formula always refer to the same lookup table.

5. Copy down, then copy across

- Copy the formula down the column to fill all rows. The lookup value (A2) should change to A3, A4, etc as you copy down.

- When you copy the formula to the right to pull other fields from the lookup table, two things matter:

- The lookup value column in your master sheet should stay column A. To keep the column fixed while allowing the row to change, use a mixed reference like `$A2`. You can create this by placing the cursor on the lookup value reference and pressing F4 until you get the column locked but the row relative.

- The table array must remain fully absolute so it does not shift at all.

- Only change the column index number to pull the next field. For example if Position was index 4, Put Outs might be index 5. Change the 4 to 5 in the formula, and the same locked table will return the other column.

## Common errors and how to fix them

- `#N/A` means no exact match was found. Check:

- Are keys identical in both tables? Watch for extra spaces, different casing, or formatting differences.

- Did you use the same concatenation order? `Player&Year` must match `Player&Year` on the other table.

- Wrong values returned:

- Did you forget to lock the table array with absolute references? If it moved when you copied, the index will point to the wrong column.

- Are you using the right column index? Count columns in the table array from left to right, starting at 1 for the first column.

- Blank returns:

- Confirm both the lookup column and the return columns contain data. If the return field is empty in the lookup table, VLOOKUP will return an empty string.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Use `TRIM` to remove stray spaces when concatenating. Example: `=TRIM(B2)&TRIM(C2)`.

- If you need case-insensitive exact matches, VLOOKUP is fine because it is not case-sensitive. If you need case-sensitive matching, use INDEX and MATCH with an exact-match workaround.

- If your lookup table might expand, convert it to a Table (Insert > Table) and use structured references or set the VLOOKUP range to the Table name to avoid updating the range manually.

- Consider using `INDEX` and `MATCH` for left lookups or when you want more flexibility. VLOOKUP always looks to the right.

## FAQ

Q: What if player names are not unique across years? How do I ensure correct matches?

A: Create a concatenated key that includes both player and year. That makes each row unique. Use `=B2&C2` or `=B2 & "_" & C2` if you want a separator.

Q: Why do I use 0 or FALSE for the last VLOOKUP argument?

A: 0 or FALSE forces an exact match. Without it, VLOOKUP may perform an approximate match and return incorrect results unless the table is sorted.

Q: How do I prevent the lookup table reference from changing when I copy the formula?

A: Use absolute references. Press F4 when the table range is selected in the formula to change it to something like `$A$1:$H$12056`.

Q: Can I use VLOOKUP to pull multiple columns at once?

A: You can copy the VLOOKUP across columns and change only the column index number for each target column. Alternatively, use INDEX/MATCH or newer functions like XLOOKUP for more convenience.

Q: What if my lookup table grows with new rows?

A: Turn the range into an Excel Table and use the table name as the lookup array, or set the table array to a range larger than current rows, then make sure new data falls inside the range.

## Summary of Key Points / Take-Home Messages

- Always create a unique key when matching on multiple fields, for example player plus year.

- Put the key in the leftmost column of your lookup table and use exact match in VLOOKUP.

- Lock the table array with absolute references and use mixed references for the lookup value when copying across.

- Only change the column index number when copying a VLOOKUP across to retrieve additional fields.

- Use TRIM and consistent formatting to avoid mismatches.