calling a cell reference

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

How do I find the cell reference of a lookup?

I have a named range of 5 columns. I'm looking in the
named range for something in the 5th column based on a
value in the first column(using vlookup). This returns
the value of the cell in column 5 corresponding to the
value in column 1. How can I get the cell reference of
that value?

tia,
Dave
 
One way,

With the lookup value in A1 and the range named testRange:

=ADDRESS(MATCH(A1,OFFSET(testRange,0,0,ROWS(testRange),1),0)+ROW(testRange)-1,CO
LUMN(testRange)+4)

HTH
Anders Silvén
 
As long as you know the column number where the returned value is to be
found, you can use this shortened formula:

Assume range is A1:E25,
Look-up value to be entered in F1,

=ADDRESS(MATCH(F1,A1:A25,0),5,4)


HTH,

RD
========================================
Please keep all correspondence within the Group, so all may benefit!
========================================
 
Hi Anders,

The lookup range is *only* 1 column, even though the data range may be 5
columns.
As per the OP, you're looking for an existing value that resides in column
1.
I chose column 1 for my example to be column A.
Even in a named range, the first column will be known, whether it's A or B
or X.
And as I stated, as long as you know the column number where the *returned*
value is to be found, in this case the fifth, the suggested formula is a
viable option.

If the named data range started in column J (assuming Row 1 to 25 also!),
the formula would be:

=ADDRESS(MATCH(F1,J1:J25,0),14,4)

OP is looking for an address, where half of the address is already known ...
it's 4 columns over from the start of the data range.
In most everyday situations, one knows the whereabouts of their data base,
which allows for my shortened formula.
If the location of the dB is unknown, then your formula must be used.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

You are correct that the formula could be shorter if the lookup range had
one
column only. It has 5 columns in this case.

With a one-column lookup range and the lookup value in A1, one way is:

=ADDRESS(MATCH(A1,testrange3,0)+ROW(testrange3)-1,5)

Also, your formula only works if the lookup range begins in A1.

Regards,
Anders Silvén
 
Back
Top