returning a cell reference

  • Thread starter Thread starter TOBY
  • Start date Start date
Toby, =CELL("address")
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
The "Match" function will return a row number.and/or a column number.

When used in conjunction with the "Address" function, you can get an actual
cell reference (address).

For example,
A1:F1 are labels,
B1:F1 contains colors: Red, Blue, Green, Brown, Yellow
A2:A10 contains sizes from 28 to 44.
B2:F10 contain different prices for each size in differing colors.

Say that your lookup values are placed in G1 & H1,
G1 for size and H1 for color.

To obtain the *PRICE* for a particular size and color, you can use the
formula:
=VLOOKUP(G1,A1:F10,MATCH(H1,A1:F1,0),0)

BUT,
To have the exact *CELL ADDRESS* returned for *this* price, use the formula:
=ADDRESS(MATCH(G1,A1:A10,0),MATCH(H1,A1:F1,0),4)
--

HTH,

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



Is there a function in excel which returns a cell
reference/address?
 
Back
Top