Here’s the problem I had to solve with Excel. (I’m sharing this in case anyone Googles a similar problem.)
I am entering a long list of data in three columns– to keep it simple, let’s call them “Client,” “Salesperson,” and a 10-digit “Salesperson ID.”
Every time I enter a salesperson’s name, I then have to also enter his ID. This gets tedious.
So I wanted Excel to think like this: “If the Salesperson name entered matches one that’s already been entered, automatically fill in the Salesperson ID based on that previous entry. If it doesn’t match, just put ‘No match’ in the box.”
Check it out, yo. Here’s the formula from 10 rows down in the table:
=VLOOKUP(B11,$B$1:$C10,2,FALSE))
This says, “Look at what’s in cell B11 (the 11th entry in the Salesperson column). If it matches a name anywhere in the existing columns B or C (from B1 to C10), then fill the cell with whatever’s in the 2nd column of that selection — that is, whatever’s in column C.”
So if I enter “Smith” in B11, it looks at the existing B and C columns for the word “Smith” (only up to row 10). If it finds it — say, in B6 — it then gives us the number that’s in C6. (It’s an Excel quirk that you have to search both columns B and C, even though only column B might have the data.)
When I put the formula in row 12, it updates so it’s searching rows 1-11. And so on, always searching above the existing entry.
But if it doesn’t find the text, it returns an error or a messy “N/A.” So I added some code to make it pretty.
=IF(ISNA(VLOOKUP(B11,$B$1:$C10,2,FALSE)) = TRUE,”No match”, VLOOKUP(B11,$B$1:$C10,2,FALSE))
This says, “If the VLOOKUP returns nothing (i.e., FALSE), then display ‘No match.’ But if it does come up with something, then display whatever value it calculates — i.e., whatever’s in column C.”
Make sense? I know. But if you’re trying to solve this problem, it does. Honest.
Steven Rumbalski says:
shorter! ;)
=IF(ISNA(MATCH(B11,$B$1:$B10,0)),”No match”, VLOOKUP(B11,$B$1:$C10,2,FALSE))