|
|
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 |