Formula for auto-fill

[Learn]

Hi,

I am thinking of creating a formula which will ignore blank cells and automatically fills regional manager's name in 'column G' according to post code in 'column B'. What is the best possible way to do it because I do not want to apply nested IF formulas. I cannot use VLOOKUP or LOOKUP funtions since it is not the first column in the table.

Thanks in advance for your help!

[Azam Ali]

Hi Learn

paste this formula in cell G2

=INDEX($A$1:$A$100,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$100,$G$1:G1,0)),0),0))

confim this formula through Ctrl+shift+Enter instead of only enter and copy and paste down

kindly upload a sample workbook if it does not help

[Learn]

Hi Azam,

Sorry, it did not work with my excel sheet. I am attaching a sample excel sheet with this reply. Basically, every post code starting with 'AB' is assigned to 'Mr X'. while post code with 'CA' is assigned to 'Mr Y'. Some post codes are also divided between two area managers, such as 'KY1' to 'KY9' are handled by 'Mr X', while 'KY10' to 'KY17' are under the supervision of 'Mr Y'. It would also help if formula ignores blank cells as well as cells not containing any post code.

Thanks again for a very quick response!

[Azam Ali]

Hi Learn

first you need to define the list of mananger seperatly based on three postcodes or there may be two list first on two post codes and second on the basis of three post codes. however in case of two list, two formulas will be used to pick the manager name.


kindly find the attatched file

[Learn]

Thank you very much, Azam! That is exactly what I was looking for. I will now try to analyse and explore the formula in more detail.

[tigertiger]

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.