Using the above arguments, the below formula for cell F2 would be appropriate:
=VLOOKUP(C2,$H$2:$I$21,2,FALSE)
Excel will return the sales rep name:
To quickly copy the formula down to all of your rows of data, double click the fill handle (the square dot) in the lower right corner of cell F2. Excel will quickly copy the formula and you will have filled in all of the rep names.
2. #N/A values indicate missing values in the lookup table
It is always possible that a new sales rep was hired since last creating the sales rep lookup table. A quick scan of the VLOOKUP column’s results is useful to find the values that returned #N/A. This indicates that the Rep ID from the invoice sheet was not found in the lookup table.
If there are too many records to easily scan, sort the dataset in descending order by the column filled with VLOOKUP. Any error cells will come to the top of the dataset in a descending sort.
In the example, it looks like a new sales rep 199 was recently hired:
Tip: When you add the new sales rep to your lookup table, insert them into the middle of the range. Your lookup table does not need to be sorted, and this will prevent you from having to reenter the VLOOKUP formula:
Dostları ilə paylaş: |
