Common VLOOKUP problem: Number entered as text Work-around: As a result, the formula cannot find the lookup value in the range G3:H8, and the #N/A error value is returned instead.įigure 4. See below example where cell D4 is entered as text, with an apostrophe at the beginning: ‘90%. One common problem with VLOOKUP is when values are mistakenly entered as text. Using VLOOKUP to calculate the bonus per employee Number entered as text The table below shows the bonus for each employee as supplied by the VLOOKUP function.įigure 3. Our formula looks up the closest match for the rating in column D in the range G3:H8, then moves to the second column to the right and obtains the corresponding grade. The dollar signs “$” in the formula fix the cells so that we can easily copy and paste the formula to other cells. Step 4 : Copy the formula in cell E3 to cells E4:E12 by clicking the “+” icon at the bottom-right corner of cell E3 and dragging it down In order to get the bonus for each employee, we follow these steps: Results will be recorded in column E.įigure 2. We want to lookup the rating and obtain the corresponding bonus per employee by using VLOOKUP. In cells G3:H8, we have a table of ratings with corresponding bonus. Our table consists of four columns: Employee ID (column B), Name (column C), Rating (column D) and Bonus (column E).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |