An exact-match lookup pulls one rep's call score from your sheet by matching that rep's unique ID, so the number belongs to that rep.
If you keep a call-score sheet for your team, you already know the slow part. A rep asks how their last two reviews went, or you have ten minutes before a 1:1, and you scroll through a long list hunting for the right person. Names get spelled differently across files. Two reps share a first name. Your eyes skip a row. Any one of those is a small problem. Across a week of coaching, they add up to wasted time and the occasional wrong number in front of a rep.
A lookup removes most of that. You type one rep's ID into a cell, and a formula returns that rep's score from the sheet. Once it is set up, the answer takes a second to get, and it comes out the same every time you ask.
This post covers three steps. First, give every rep one clean ID column. Second, build a lookup that matches the ID exactly. Third, check the result before you use it in a review. The examples use spreadsheet lookup functions, since most score sheets already live in a spreadsheet. You do not need an add-on, a data team, or a new tool. You need the sheet you already keep, a few minutes, and two reps whose scores you already know so you can test the result.
Step 1: Give every rep one clean ID column
A lookup needs a key to search on, and that key has to point to exactly one rep. Names are a weak key. People get spelled differently across sheets, two reps can share a first name, and a name can change. An ID is meant to stay the same for the life of the rep's record.
Microsoft's VLOOKUP help page uses a similar case as its example: find an employee name based on their employee ID. That is the job here, with a call score in place of a name.
Copy the IDs from the system where they were issued, such as an HR export or your CRM, rather than retyping them by hand. Retyping invites typos, and one wrong digit gives you a lookup that finds nothing or, worse, finds the wrong rep.
Next, arrange the columns so the ID sits where the lookup can reach it. The function reads the key in the first column of the search range, then moves right to find the value to return. Microsoft's help page is direct about this: “The value you want to look up must be in the first column of the range of cells you specify in the table_array argument.” Google's page states the same limit from the other side: the function can only search the key column or columns further to the right of it. See Google's VLOOKUP page. If your scores sit to the left of the IDs, the lookup cannot reach them. Move the ID column to the front, or copy the IDs into a new first column.
A layout that works well for this job is simple. As Microsoft puts it, “The secret to VLOOKUP is to organize your data so that the value you look up (Fruit) is to the left of the return value (Amount) you want to find.” In your sheet, the ID plays the Fruit and the call score plays the Amount:
- Column A: Rep ID
- Column B: Rep name
- Column C: Call score
- Column D: Review period or call date
Give the header row plain labels that match how you talk about the numbers in conversation. Labels help when you come back to the sheet after a month away, and they make it easier for someone else on your team to read it. Avoid merged cells in the data area. A merged cell can hide the row a value really sits in, and a lookup reads cells as they are, not as they appear on screen.
If a rep moves to another team, keep their ID the same and update the team column rather than creating a new row. A second row for the same person splits their history across two entries, and the lookup has no way to know they are the same rep.
Keep the ID in one format. If some IDs are numbers and others are text, or some cells carry an extra space at the start or end, the lookup can miss a rep who is clearly in the sheet. Pick one format, clean the whole column once, and do the same for any new rows you add.
Then decide what one row means. A lookup returns a value for one key, so if a rep has several call scores in the same review period, the sheet has to be set up for that. Choose first whether you want the latest score or an average ( Rep call score averages: 3 values to compare), and keep one row for each rep ID so the lookup has one answer to return. Then build the sheet to match. The choice belongs in the layout, not hidden inside the formula.
If your call scores live in a CRM rather than a spreadsheet, the same idea often appears as linked records, where a call points to the rep it belongs to. EspoCRM's formula documentation explains that you can reach attributes of related entities with the expression linkName.attributeName. That is the CRM version of reaching across to the right column. Check your own tool's documentation for its equivalent, and still keep a clean, stable ID on each rep record, since that ID does the same job it does in a sheet.
Step 2: Build the lookup with an exact match
Put the ID you want to search for in its own cell, away from the data. Label it so you can find it later, for example "Rep ID" in F1 with the ID to look up in G1. Google's help page notes that you can use a cell reference for the search key: “If you don’t want to fill a value for search keys, you can also use a cell reference, for example "G9."” A cell reference means you change one cell to switch reps, and every formula that reads that cell updates with the change.
Now the formula for this layout:
=VLOOKUP(G1, A:D, 3, FALSE)
Each part has one job. The first part, G1, is the ID you are searching for. The second part, A:D, is the range the function searches, and it starts with the ID column. The third part, 3, is the column that holds the value you want back. Google explains how to count it: “imagine that the columns of the range are numbered from left to right and start from 1.” In A:D, column A is 1, column B is 2, and column C is 3, so 3 returns the call score. The last part, FALSE, matters most of all.
Here is a worked example. Say one rep sits partway down the sheet, with the score in column C. You type that rep's ID into G1. The function scans the ID column for that ID, finds the matching row, and returns the value in the third column of the range. If you then type an ID that is not in the sheet, you see an error instead of a number, which tells you to check the sheet rather than trust a value.
It is worth using a lookup at all, not just a filter. You could filter the sheet by ID for a single look, and that works. The lookup earns its place when you need the same answer repeatedly, when you pull several reps in one sitting, or when someone else on the team needs a score without knowing how the sheet is laid out.
FALSE asks for an exact match. Setting it to TRUE asks for an approximate match, and that behaves very differently. Microsoft describes the approximate option this way: “Approximate match - 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value.” That is a poor fit for IDs. An ID is a label, not a ranking. A nearby ID number has no reason to belong to a nearby rep, so the closest value the function finds may belong to someone else. With FALSE, the function returns a row only when the ID matches exactly.
Decide how far the range goes. Whole-column references like A:D keep the formula working as the team grows, and they do not need editing when a new rep is added. A fixed range with a set last row is tidy, but it stops there, so a rep added below that row will not show up. If you choose a fixed range, leave room below the last rep and recheck the formula when the list grows.
Add a second lookup for the rep name, using the same ID cell and the same range, with 2 as the column number. That gives you a quick check that the ID points at the rep you expect. It is the most useful check in the whole process, because a name that does not match the person you had in mind tells you something is wrong before you read a score.
Copy the formula down the column if you want one lookup for each row. A relative reference to G1 moves to G2, G3 and so on as you copy, so give each row its own ID cell first, or anchor the ID cell as $G$1 if every copy should read the same one. Then look at the formula in a second row before you trust it.
When you paste an ID from another system into G1, check it for stray spaces or a leading apostrophe. Those characters are invisible on screen, and they make an ID that looks correct fail to match.
Test it before you rely on it. Pick two reps whose scores you already know from the original data, one near the top of the sheet and one near the bottom. Type each ID into G1 and confirm the score matches. Testing both ends matters, because a range that is set too short can miss rows at the bottom, and that problem only shows up once you need one of those reps.
Name the cells, too, if your spreadsheet supports it. A label such as "Rep ID" beside the lookup cell tells the next reader what the formula is waiting for. A short note on the formula cell, or a comment that says which columns the range covers, saves the next person from guessing.
Step 3: Check the result before you use it in a review
A lookup that finds nothing does not return a zero. Google's example shows the behavior: “For example, the fruit name for ID = 103 is "Banana." If there’s no exact match, you get a #N/A error.” A rep whose ID is missing or mistyped shows an error, not a blank or a low score. That is useful. A zero that looks like a real score would be worse than an error in a coaching conversation, because it invites a discussion about a number that was never there.
If you want a cleaner view, you can wrap the formula so the cell shows a short note in place of the error. That is a reasonable choice, but make it on purpose. The note should say what happened, such as "ID not found, check the sheet," and it should never look like a score. Hiding the error completely is how a rep with a typo in their ID quietly drops out of your review.
Next, confirm the row, not just the number. Before a 1:1, look at three things: the rep name next to the ID, the score itself, and the date or review period in the same source row. Make sure the period matches the one you plan to discuss. A score from the previous period that sits in a cell that looks current is an easy way to walk into a review with the wrong facts.
Most problems fall into a few patterns, and each one has a clear fix:
| What you see | Likely cause | What to do |
|---|---|---|
| An error such as #N/A | The ID is missing, mistyped, or has a stray space | Fix the ID in the source sheet, then retest it |
| A name that is not the rep you expected | The ID is attached to the wrong row | Stop and compare the ID with the original system |
| A score from an earlier period | Two rows share one ID, so the lookup can return a row from the wrong period | Merge or remove the duplicate, then retest |
| Name, score, and period all look right | The lookup is working as designed | Use the score, and note the date you pulled it |
Keep the source sheet as the only place where scores are entered. The lookup cells should hold formulas and nothing else. If a lookup cell holds a typed number, it stops following the source, and nothing on the screen tells you that it has stopped. Check that the cells read as formulas when you open the file.
Scores can also change after a review is re-scored or corrected. When you prepare for a meeting, note the date you pulled the number in your prep notes. If the score changes later, you will know whether the figure you used was the current one.
If reps can see the sheet, decide whether they also see the lookup cells. A shared view with the ID cell and the result can help a rep check their own numbers before a conversation. Limit editing rights on the lookup cells, though, so nobody overwrites a formula by accident. Protecting those cells is a small step that prevents a lot of confusion later.
When you open the 1:1 with the number on screen, treat it as a starting point rather than a verdict. The score points you to where to look, but it cannot explain why a call went the way it did. Pull up the call notes or recording for that period, compare what the score rewards with what happened on the call, and bring that comparison to the conversation. That keeps the discussion specific and gives the rep something concrete to work on.
Last, build a short routine. The checks below repeat whenever the sheet changes. Each one takes a minute or two, and together they protect the one thing a 1:1 depends on, which is the right number for the right person.
| When this happens | Check this |
|---|---|
| A new rep joins | Add their row to the source, then test the lookup with their ID |
| The team changes its ID format | Clean the whole ID column, then retest the list |
| A score is re-scored or corrected | Pull the number again and update your prep notes |
| Before any 1:1 | Confirm the name, score, and period sit in the same row |
Related guides: Coaching follow-up tasks with 5 evidence checks · Departing rep's Mac: 3 steps to prepare and erase.