# Excel 2007 formula required

#### goyalsr

##### New Member
i have a table like this
BANKNIFTY
BANKNIFTY
BANKNIFTY
BANKNIFTY
BANKNIFTY
BANKNIFTY
NIFTY
NIFTY
NIFTY

i want to find address of the cell on which a text occurs first time and last time in a column.eg in above address of first row and sixth row in case of banknifty, and 7th row and 9th row in case of Nifty. i tried using match function , but it always gives last row number irrespective of my search.please note that data is very big ,above is just a part of that please guide.
I require this for calculation of option pain in a stock.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Ken Cowen

##### Board Regular
Assume data in column g rows 1 through 7

=MATCH(G1,\$G\$1:\$G\$7,FALSE) should give you the first one

=MATCH(G1,\$G\$1:\$G\$7) should give you the last one

I hope this helps.

Ken

#### Trebor76

##### Well-known Member
Hi goyalsr,

Welcome to MrExcel!!

Assuming the data starts at A1 and goes down column A (change to suit), put the following formula in Row 1 of any unused column...

...and copy it down to the last Row used in column A.

HTH

Robert

Last edited:

#### goyalsr

##### New Member
Assume data in column g rows 1 through 7

=MATCH(G1,\$G\$1:\$G\$7,FALSE) should give you the first one

=MATCH(G1,\$G\$1:\$G\$7) should give you the last one

I hope this helps.

Ken
Thanks sir for reply.As i my data is large so i treid
=MATCH(G1,G:G,FALSE)
=MATCH(G1,G:G)

however result is not matching, i think its matching only few alphabets.Please guide sir

#### goyalsr

##### New Member

Hi goyalsr,

Welcome to MrExcel!!

Assuming the data starts at A1 and goes down column A (change to suit), put the following formula in Row 1 of any unused column...

...and copy it down to the last Row used in column A.

HTH

Robert

Thanks sir for your kind response.Result is perfect sir, however i want result in two columns against each row, the first colum gives address of first occurrence and another
colum gives address of last occurrence.
Can you please further help.
Thanks

#### Trebor76

##### Well-known Member
Formula for first change:

Formula for last change:

#### goyalsr

##### New Member
Formula for first change:

Formula for last change:

Thanks Sir for your kind cooperation and Support

Replies
1
Views
58
Replies
9
Views
97
Replies
5
Views
86
Replies
1
Views
79
Replies
18
Views
200

1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
• Auto-Create a monthly Sign in sheet for preschool students
The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
• VBA vlookup multiple results
Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
• Extract values for earliest/latest times
I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...