USING VLOOKUP TO RETURN A CELL ADDRESS

[Guest]

I am trying to use the VLOOKUP command but want it to return a cell address
rather than the result, i tried ADDRESS but to no avail as i wouldn't know
its position with the worksheet

 

[Guest]

Say your lookup table is in A1:B7 and the lookup value is in A9 and say that
you normally lookup in column B(1 column offset from column A) then you will
use the following to get the address of the same cell.

=ADDRESS(ROW(A1:A7)+MATCH(A9,A1:A7,FALSE)-1,COLUMN(A1:A7)+1)

 

[Guest]

The usual technique is to VLOOKUP() to determine the value and then use the
MATCH() function to return the location of the value ( if you know the row
and column this is equivalent to the address)

 

[OX_Gambit]

I need the same thing but I have multiple cells witht he same value in it and
need to find the last row that contains a value.

ex. looking for the last value of 1005 in the array
1005
1005
1005
1006
1006
1007

This should come out as 3.

Thank you for your help.

 

[T. Valko]

This should come out as 3.

Do you want the actual row number or the *relative* row number?

If your data was in the range A1:A6 then the last instance of 1005 is in row
3. If your data was in the range A2:A7 then the last instance of 1007 is in
row 4.

This array formula** will return the *relative* row number:

=MAX((A2:A7=1005)*ROW(A2:A7))-MIN(ROW(A2:A7))+1

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This normally entered formula will also return the *relative* row number:

=LOOKUP(2,1/(A2:A7=1005),ROW(A2:A7))-MIN(ROW(A2:A7))+1

 

[Ashish Mathur]

Hi,

Say you have the array in range D510. In E5, type the following formula
=D5&COUNTIF($D$55,D5) and copy down till E10. In cell D12, type 1005 and
in cell E12, type the following formula

=ADDRESS(MATCH(D12&COUNTIF($D$5:$D$10,D12),E1:E10,0),4)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com