OT Excel help

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
Location
DE4, Derbyshire
'Ctrl'+F will find the cell - is that any use?
 

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.
 

simon_adams_uk

Veteran
Location
SW London
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
simon_adams_uk said:
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
lifeson

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")
.Range("K" & iRow).Value = Me.txtAddress1.Text
End With

Thats took me all day :tongue:
 

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.
 
Top Bottom