# OT Excel help

#### lifeson

##### New Member
Does anybody know what formula to use in excel to return the row number of a row that contains specific data in column A

eg
Column "A" contains unique IDs (Pay numbers)

I want a formula that tellls me which row contains pay no 1234:confused:

#### And

##### Fun sponge
'Ctrl'+F will find the cell - is that any use?

##### Veteran
i would use the search function rather than writing a formula

this may be no use to you

#### bonj2

##### Guest
lifeson said:
Does anybody know what formula to use in excel to return the row number of a row that contains specific data in column A

eg
Column "A" contains unique IDs (Pay numbers)

I want a formula that tellls me which row contains pay no 1234:confused:

You want VLOOKUP.
It can only return the corresponding value in another column, not the actual number of the row. But you can just create another column with just the number of the row.
Be aware though that your pay numbers will havew to be in alphabetical order for it to work.

##### Guru
Add a new column to the right of the column which contains the unique pay number IDs. This new column should be numbered from 1 to n.

For this example they're columns A (Pay number IDs) and B (row number) and you've got 100 records (ie 100 rows).

If you enter a formula along the lines of
=VLOOKUP(1234,A1:B100,2,FALSE)
will give you the row number for ID 1234

Instead of typing '1234' you can softcode this to another cell value.

I disagree with Bonj that the pay number IDs have to be in order - as long as they are unique it shouldn't matter.

HTH,
S

#### bonj2

##### Guest
Add a new column to the right of the column which contains the unique pay number IDs. This new column should be numbered from 1 to n.

For this example they're columns A (Pay number IDs) and B (row number) and you've got 100 records (ie 100 rows).

If you enter a formula along the lines of
=VLOOKUP(1234,A1:B100,2,FALSE)
will give you the row number for ID 1234

Instead of typing '1234' you can softcode this to another cell value.

I disagree with Bonj that the pay number IDs have to be in order - as long as they are unique it shouldn't matter.

HTH,
S
hmmm... maybe they don't. I vaguely remember reading it though, maybe it was something else.

OP
OP

#### lifeson

##### New Member
I did it this way in the end:

in VBA

Dim iRow As Long
iRow = Application.Match(Val(txtPayNo.Text), .Columns(1), 0)

Then used the row ID and the column reference to find the cell I wanted

e.g.

With Worksheets("Data")
End With

Thats took me all day

#### Keith Oates

##### Janner
Yes, but you will remember it now. I always feel good when I've finally cracked something like that!!!!!!!!!!!!!!

#### bonj2

##### Guest
if you need any more simple things like that doing feel free to pm me, you can email me your workbook it would almost certainly take me far less than all day.

OP
OP

#### lifeson

##### New Member
bonj said:
if you need any more simple things like that doing feel free to pm me, you can email me your workbook it would almost certainly take me far less than all day.

You may live to regret that offer :?: