I currently have a spread sheet with the following Fields.
<tbody>
</tbody>
And the following forumalee search the infomation to give me this
<tbody>
</tbody>
<tbody>
</tbody>Area 50007112
However my problem is this.
I need a foumale which will search the covering engineer coloum and displayon my search form if the covering engneer is covering more than one area engineer area as if you look at the information above you will see covering engineer 7106 is covering two engineers areas. hope this make sense
Also how do I stop the foumales above for displaying 0 I just need them to display NA. I use this forumale because index looks left and right where by vlookup does not. also how do i stop the foumales above from displaying 0 when all i want them to display if no info is na
| 50005008 | Aberdeen 201F47000 | Scotland/North/Northeast | Vacant | Speak to branch | |
| 50007422 | Aberdeen 201F47000 | Scotland/North/Northeast | Vacant | 50007449 till 06/12 | |
| Aberdeen 201F47000 | Scotland/North/Northeast | 50007449 | 07776225417 | ||
| Aberdeen 201F47000 | Scotland/North/Northeast | ||||
| 50007271 | Edinburgh 201F48000 | Scotland/North/Northeast | Vacant | DG1-3 - 5164. DG4-9 - 7270. DG10-16 - 5165 | |
| 50004010 | Edinburgh 201F48000 | Scotland/North/Northeast | Vacant | DG1-3 - 5164. DG4-9 - 7270. DG10-16 - 5165 | |
| Edinburgh 201F48000 | Scotland/North/Northeast | Seafood company / pinneys | 50007270 | 07791712663 | |
| Edinburgh 201F48000 | Scotland/North/Northeast | ||||
| 50005057 | Glasgow 201F41000 | Scotland/North/Northeast | Vacant | 50005046/50007328 | |
| 50005045 | Glasgow 201F41000 | Scotland/North/Northeast | BDE Engineer | Check engineer sheet for postcode | |
| Glasgow 201F41000 | Scotland/North/Northeast | City Building Glasgow | Speak to Branch | ||
| Glasgow 201F41000 | Scotland/North/Northeast | Stobhill/New Victoria Hospital | Speak to Branch | ||
| Glasgow 201F41000 | Scotland/North/Northeast | D&A CALLS (PA'S/KA'S. G's tel 1st) | 50005046 | ||
| Glasgow 201F41000 | Scotland/North/Northeast | D&A CALLS (FK'S/ML'S. G's tel 1st) | 50005046 | ||
| 50007328 | Glasgow 201F41000 | Scotland/North/Northeast | e/l testing all week no callouts | 50005046 | |
| Glasgow 201F41000 | Scotland/North/Northeast | ||||
| 50004942 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | Speak to Branch | |
| 50005565 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | 50005566 | |
| 50004955 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | Check engineer sheet for postcode | |
| 50004939 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | Speak to Branch | |
| 50004948 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | 50004949 | |
| 50007434 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | 50004944 | 07505888516 |
| 50004947 | Leeds 201F32000 | Scotland/North/Northeast | BDE Engineer | 50007696 | 07775010186 |
| 50004945 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | Speak to Branch | |
| Leeds 201F32000 | Scotland/North/Northeast | ITV YORKSHIRE | 50004951 | ||
| Leeds 201F32000 | Scotland/North/Northeast | Pennine housing | 50004941 | 07791712658 | |
| 50004938 | Leeds 201F32000 | Scotland/North/Northeast | Temp area change | 50007696 | |
| 50004952 | Leeds 201F32000 | Scotland/North/Northeast | Vacant | 50004938 | 01215205191 |
| Leeds 201F32000 | Scotland/North/Northeast | ||||
| 50005300 | Liverpool 201F45000 | Scotland/North/Northeast | Vacant | 50005303 | |
| 50005299 | Liverpool 201F45000 | Scotland/North/Northeast | Vacant | Speak to Branch | |
| Liverpool 201F45000 | Scotland/North/Northeast | Knowsley M.B.C | See postcode list, send to area eng. | ||
| 50003790 | Liverpool 201F45000 | Scotland/North/Northeast | Vacant | Speak to branch | |
| 50007310 | Liverpool 201F45000 | Scotland/North/Northeast | Vacant | 50005305 (1st) / 50005303 | |
| Liverpool 201F45000 | Scotland/North/Northeast | ||||
| 50004982 | Newcastle 201F42000 | Scotland/North/Northeast | BDE Engineer | Speak to Branch | |
| 50004981 | Newcastle 201F42000 | Scotland/North/Northeast | Vacant | Speak to branch | |
| 50004987 | Newcastle 201F42000 | Scotland/North/Northeast | Vacant | 50004985 | |
| Newcastle 201F42000 | Scotland/North/Northeast | 50004984 | 07795813105 | ||
| 50004985 | Newcastle 201F42000 | Scotland/North/Northeast | sick until further notice | 50004983 | |
| Newcastle 201F42000 | Scotland/North/Northeast | ||||
| 50007174 | Birmingham 201F33000 | Midlands/North Region | Vacant | Speak to Branch | |
| 50005217 | Birmingham 201F33000 | Midlands/North Region | Vacant | Speak to Branch | |
| 50005251 | Birmingham 201F33000 | Midlands/North Region | Vacant | 50007404 | |
| 50005244 | Birmingham 201F33000 | Midlands/North Region | Service Manager | 50005253 | |
| Birmingham 201F33000 | Midlands/North Region | Veolia sites | 50005254 | ||
| Birmingham 201F33000 | Midlands/North Region | ||||
| 50005281 | Blackburn 201F46000 | Midlands/North Region | Vacant | 50005276 | |
| Blackburn 201F46000 | Midlands/North Region | Bury M.B.C. | 50005274 | ||
| Blackburn 201F46000 | Midlands/North Region | 50001988 | 07790483561 | ||
| 50008104 | Blackburn 201F46000 | Midlands/North Region | holiday | 50008103 | |
| Blackburn 201F46000 | Midlands/North Region | ||||
| Blackburn 201F46000 | Midlands/North Region | ||||
| 50007070 | Coventry 201F35000 | Midlands/North Region | Vacant | 50004921 | |
| 50007341 | Coventry 201F35000 | Midlands/North Region | Vacant | 50005539 | |
| Midlands/North Region | |||||
| 50005345 | Manchester 201F31000 | Midlands/North Region | Vacant | 50003797 | |
| 50005337 | Manchester 201F31000 | Midlands/North Region | Vacant | Speak to Branch | |
| 50005283 | Manchester 201F31000 | Midlands/North Region | Vacant | 50006508 | |
| 50005350 | Manchester 201F31000 | Midlands/North Region | Holiday | 50005347 | |
| 50005323 | Nottingham 201F76000 | Midlands/North Region | Vacant | See postcode on eng sheet | |
| 50005324 | Nottingham 201F76000 | Midlands/North Region | Vacant | See postcode on eng sheet | |
| Midlands/North Region | 50007262 | 07920766744 | |||
| Midlands/North Region | 50007668 | 07432723291 | |||
| Midlands/North Region | |||||
| 50005368 | Sheffield 201F43000 | Midlands/North Region | Vacant | 50007578 | 07766582983 |
| Midlands/North Region | |||||
| Bristol 201F21000 | Southwest Region | ||||
| Southwest Region | |||||
| 50007109 | Cardiff 201F22000 | Southwest Region | Vacant | Speak to Paul Thomas | |
| 50007104 | Cardiff 201F22000 | Southwest Region | Vacant | 50007111 | |
| 50007112 | Cardiff 201F22000 | Southwest Region | Vacant | 50007106 | |
| 50007384 | Cardiff 201F22000 | Southwest Region | holiday | 50007106 | |
| Southwest Region | |||||
| 50007275 | Luton 201F74000 | Southwest Region | Working at Cambridge | 50004633 / 50004635 | |
| 50004640 | Luton 201F74000 | Southwest Region | Vacant | 50004633 / 50004635 | |
| 50004638 | Luton 201F74000 | Southwest Region | Vacant | 50004636 | |
| Southwest Region | |||||
| 50007175 | Plymouth 201F23000 | Southwest Region | Unavailable | Speak to Branch | |
| 50006079 | Plymouth 201F23000 | Southwest Region | Vacant | 50006078 | |
| 50007385 | Plymouth 201F23000 | Southwest Region | Vacant | Speak to branch | |
| Southwest Region | 50007593 | 07917241230 | |||
| Southwest Region | |||||
| 50004664 | SouthCoast 201F75000 | Southwest Region | Small works engineer | 50007074 | |
| SouthCoast 201F75000 | Southwest Region | Hampshire partnership trust NHS | 50004667 | ||
| 50004665 | SouthCoast 201F75000 | Southwest Region | holiday | speak to stuart fisher / branch | |
| Southwest Region | |||||
| ThamesValley 201F24000 | Southwest Region | ||||
| Southwest Region | |||||
| 50004528 | Basildon 201F12000 | London/Southeast Region | suspended | 50007358 | |
| London/Southeast Region | |||||
| London/Southeast Region | |||||
| 50004555 | Cambridge 201F14000 | London/Southeast Region | Vacant | 50007275 | |
| 50004558 | Cambridge 201F14000 | London/Southeast Region | Vacant | 50004559 | |
| Inviron | Cambridge 201F14000 | London/Southeast Region | Dispatched by account manager do not dispatch | Speak to Rebecca @ branch | |
| London/Southeast Region | |||||
| 50007276 | Croydon 201F13000 | London/Southeast Region | Vacant | Speak to Mark Smith | |
| 50004596 | Croydon 201F13000 | London/Southeast Region | Vacant | 50004602 | |
| 50004603 | Croydon 201F13000 | London/Southeast Region | Vacant | 50004703 | |
| Croydon 201F13000 | London/Southeast Region | Wandle House Calls | 50004601 | ||
| 50004598 | Croydon 201F13000 | London/Southeast Region | sick? | 50004602 | |
| London/Southeast Region | |||||
| Maidstone 201F29000 | London/Southeast Region | ||||
| London/Southeast Region | |||||
| 50004821 | Tottenham 201F11000 | London/Southeast Region | Vacant | Speak to Pam Colbert | |
| 50004832 | Tottenham 201F11000 | London/Southeast Region | Vacant | 50004532 | |
| Tottenham 201F11000 | London/Southeast Region | Poplar Harca Calls | 50004680 | ||
| 50004604 | Tottenham 201F11000 | London/Southeast Region | Covers N2,N3,N6,N10,N12,N20,NW2, NW4, NW7, NW8, NW9, NW11 | ||
| 50004793 | Tottenham 201F11000 | London/Southeast Region | Small works engineer | N1 - 4680. All other areas - Speak to branch | |
| 50004789 | Tottenham 201F11000 | London/Southeast Region | Paternity | 50004795 | |
| 50004795 | Tottenham 201F11000 | London/Southeast Region | holiday | test | |
| Tottenham 201F11000 | London/Southeast Region | ||||
| Tottenham 201F11000 | London/Southeast Region |
<tbody>
</tbody>
And the following forumalee search the infomation to give me this
| =IFERROR(INDEX(B:B,IFERROR(MATCH(J$3,A:A,FALSE),MATCH(J$3,E:E,FALSE))),"N/A") |
| =IFERROR(INDEX(C:C,IFERROR(MATCH(J$3,A:A,FALSE),MATCH(J$3,E:E,FALSE))),"N/A") |
| =IFERROR(INDEX(D:D,IFERROR(MATCH(J$3,A:A,FALSE),MATCH(J$3,E:E,FALSE))),"N/A") |
| =IFERROR(INDEX(E:E,IFERROR(MATCH(J$3,A:A,FALSE),MATCH(J$3,E:E,FALSE))),"N/A") |
| =IFERROR(INDEX(F:F,IFERROR(MATCH(J$3,A:A,FALSE),MATCH(J$3,E:E,FALSE))),"N/A") |
| =IFERROR(INDEX(A:A,IFERROR(MATCH(J$3,A:A,FALSE),MATCH(J$3,E:E,FALSE))),"N/A") |
<tbody>
</tbody>
<tbody> </tbody> |
<tbody>
</tbody>
However my problem is this.
I need a foumale which will search the covering engineer coloum and displayon my search form if the covering engneer is covering more than one area engineer area as if you look at the information above you will see covering engineer 7106 is covering two engineers areas. hope this make sense
Also how do I stop the foumales above for displaying 0 I just need them to display NA. I use this forumale because index looks left and right where by vlookup does not. also how do i stop the foumales above from displaying 0 when all i want them to display if no info is na
