Jock,

I'm confused, Can you confirm which column you want to lookup and return the
row of, A or B

Mike


"Jock" wrote:

Thanks for this input, nearly there.....

If I put '5' B4, then C1 gives the correct row re, i.e. - 4.
Another 5 in 'B9' gives the same row ref in C2. (that is, 4)

A '5' in 'A4' puts "4" in C3
A '5' in 'A9' puts a "4" in C4
Removing the "5" from 'B9' changed C3 and C4 to '#N/A' and '#Ref!'
respectively.
hth
--
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi Jock,

Put this in a cell and drag down. In the first cell it will return the row
number of the first instance and in the second row the second etc. When it
stops finding matches it will reutrn Ref errors.

=MATCH(INDEX($A$1:$B$10,SMALL(IF($A$1:$B$10=$C$1,R OW($A$1:$B$10)-ROW($A$1)+1,ROW($B$10)+1),ROW(A1)),2),$B$1:$B$10,0 )

It's an array so Ctrl+Shift+Enter.

Any more surprises:)

Mike

"Jock" wrote:

So far, so good.
It returns the first instance the criteria is matched rather than listing
them if there's more than one.
Also, could the lookup value be something like 5 so it returns all row
numbers for values greater than, in this case, 5?
--
Traa Dy Liooar

Jock


"Mike H" wrote:

Jock

Array is A1 to B10 & lookup value in C1, Try:-

=MATCH((VLOOKUP(C1,A1:B10,2,FALSE)),B1:B10,0)

Mike

"Jock" wrote:

Can I have the row number returned by VLOOKUP when it finds the correct data?
--
Traa Dy Liooar

Jock