Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Tricky Excel Formula Problem

  • 07-06-2007 03:45PM
    #1
    cooperguy
    Registered Users, Registered Users 2 Posts: 2,651 ✭✭✭


    I am trying to get excel to return the address of a cell to another cell e.g. I have a column of numbers and in this column is the number 72. I want excel to tell me that the number 72 is in Cell A20. Any idea how to get it to do that. I know how to get it to lookup the number 72 in a column, this would do it : =LOOKUP(72,A1:A30) but instead of it returning the number 72 to the cell that formula is in I want the Cell Address.

    This is part of a much bigger formula and im stuck on this one part. Any help would be greatly appreciated.


Comments

  • #2
    setanta5
    Closed Accounts Posts: 348 ✭✭setanta5


    I'm not sure without seeing the spreadsheet but you might be looking for index function. If you bring up the formula menus you should be able to click on all lookup formulas. You should be able to pick out the one you need.


  • #3
    5uspect
    Moderators, Arts Moderators Posts: 10,594 Mod ✭✭✭✭5uspect


    I'd use the CELL function in an IF function.
    If your data in in column A paste this into the first cell of another column and use the fill handle to drag it down.
    =IF(A1=72,CELL("address",A1),"")

    This will stick the cell reference beside the cell containing 72 and leave all others blank. If you want to lookup the value and place it somewhere in particular in the worksheet that complicates things a bit.


Advertisement