Dear All,
Hi! I am trying to set up an Excel based formula, which looks up a particular "text or value" - which is repeated down a column numerous times. I realize that such an exercise is best done using a loop macro in Visual Basic, but I need a non-VB based solution.
The attached file shows data on Sheet 1. I have three main columns, labelled, "First", "Last" and "Number". I want Excel to go down the first column and look for any number of times "john" is written and then display it outside in individual cells. The array formula I have come up with is shown in CELL E2. Similarly, F2 then picks up the corresponding value to E2.
The difiiculty I am having is when I add new rows to the top of the sheet. Such that what used to be A1 becomes A2, A2 becomes A3 and so on...it completely throws the formulas in columns E and F off. Can someone please tell me how I can amend the formula so that when I add the rows on the top of the worksheet the array continues to work?
Many thanks,
Y.
Bookmarks