Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Tricky Excel Formula Problem

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


    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,552 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