|
|
|
| This uses VLOOKUP to look for a value within a range and then report a value from a different column Note the fact that column D Assigned has been copied and pasted to form column A - this is because the value searched must be sorted in ascending order and must be located in the leftmost column.
|
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Assigned To |
Batch |
Level |
Assigned |
Formula |
Explanation |
| 2 | ann | 23456 | no problems | ann | =VLOOKUP(A2,A2:C7,2) | <= Looks for ann within range A2 to B7 and reports value from column B (the ,2 ) if found |
| 3 | dick | 56789 | tiny problems | dick | =VLOOKUP(A6,A2:C7,3) | <= Looks for rob within range A2 to B7 and reports value from column C (the ,3 ) if found |
| 4 | harry | 67890 | some problems | harry | ||
| 5 | paul | 12345 | big problems | paul | ||
| 6 | rob | 34567 | major problems | rob | ||
| 7 | tom | 45678 | mega problems | tom |
So having completed the above sheet's content, when run the result is like this
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Assigned To |
Batch |
Level |
Assigned |
Formula |
| 2 | ann | 23456 | no problems | ann | 23456 |
| 3 | dick | 56789 | tiny problems | dick | major problems |
| 4 | harry | 67890 | some problems | harry | |
| 5 | paul | 12345 | big problems | paul | |
| 6 | rob | 34567 | major problems | rob | |
| 7 | tom | 45678 | mega problems | tom |