LOOKUP function to return row number

[ameliiie]

Hello,

I'm writing a formula that's supposed to use VLOOKUP and OFFSET to rearrange a matrix for bookkeeping.
Sheet1 contains the original data, Sheet2 is the new sheet. From Sheet two I want to lookup a certain category name in Sheet1 and use the row number of the row that contains the name.
Initially I started using ROW(VLOOKUP(...)) but realised that VLOOPKUP returns the value, not the cell.
Any idea how I can get the row?
Thanks for any tips!


In more detail:
My data is organised somewhat like the following abc example. I now use offset to establish a matrix over A.a-c. Then find the category name I need to do the value lookup in and the category head row to knnow by how many rows to offset the lookup matrix. Transposing doesn't work, because the matrix must remain easily handleable with using Excel matrix commands.

Sheet 1:
A
a valueAa
b valueAb
c valueAc
B
a valueBa
b valueBb
c valueBc

Sheet 2:
A a valueAa
B a value Ba

A b valueAb
B b value Bb

A c valueAc
B c valueBc <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

<!-- controls -->​

 

[Andrew Poulsom]

Use the MATCH function to return the position of an item within an array.

 

[ameliiie]

should work, thanks!