+ Reply to Thread
Results 1 to 5 of 5

Using formula as part of cell address

  1. #1
    Registered User
    Join Date
    02-16-2014
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Using formula as part of cell address

    Good evening experts,
    I have an Excel 2007 spreadsheet as follows:
    A.................B........C........D........E........F......G......H.....I
    1 Cat...........10.......40.....14G
    2 Rat...........12.......80.....15H
    3 Mouse.......14.....160.....16I
    4 Dog..........16......320....17K
    5 Bird..........18......640....18R
    6 Fish..........20....1280....19T
    7 Monkey.....22....2560....20S
    8 Snake.......24....3500....21P

    When I place the following formula in cell E1: Formula: =MATCH("*dog*",A1:A8,0) Results: 4
    The animal 'Dog' does not always appear on row 4 so whatever row it does appear on I want to paste the values of all cells to the right of it in the cells on row 1
    In this case: I want to place the values of cells B4, C4, and D4 into cells F1, G1, and H1
    In cell F1 I tried the formula ="b"&MATCH("*dog*",A1:A8,0) but it does not work
    I realize that I might have to do this via some code in the Visual Basic window, calling this code via a command button (which I have tried)
    I am open to anything you can do to help, but I am restricted to working within the confines of the Excel Workbook.

    Thanks ,
    Jim
    Last edited by jcaster; 02-16-2014 at 02:22 AM.

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Using formula as part of cell address

    hi
    you can use Index and match
    =INDEX($A$1:$D$8,MATCH("*dog*",$A$1:$A$8,0),2)

    or VLOOKUP

    =VLOOKUP("*dog*",$A$1:$D$8,2,FALSE)

    that's for values in column B, replace number column for C and D
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using formula as part of cell address

    Hi and welcome to the forum

    If Dog is the complete string you are searching for, you do not need to use *dog*...that would be used if you were searching for "watchdog" or "dogfood" or "123dog456" - the *dog* acts as wild cards

    Although burans formula is spot-on, and is probably the best 1 to use (If you had headings, you could add another match so you dont need to change the 2), here is another option that would build on what you tried to do with the "="b"&MATCH("*dog*",A1:A8,0)" using INDIRECT()

    =INDIRECT("B"&MATCH("*dog*",A1:A8,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    02-16-2014
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Using formula as part of cell address

    Good Morning Buran
    Thank you ever so much. The "=INDEX' formula works exactly as I hoped for
    The "=VLOOKUP" does locate the cell with the word 'dog' in it but does not populate the other cells like the "=INDEX" formula
    The bottom line is THANKS for giving me a solution that works

  5. #5
    Registered User
    Join Date
    02-16-2014
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Using formula as part of cell address

    Good morning FDibbins,

    Thank you for your comments, and sending me another solution that works excellent (Buran having sent one too)
    The information about the wild cards, while I was aware of this, it was good that you included it for the benefit of others, and myself had I not been aware.
    In my case, the spreadsheet information that I used in my submittal was only a test worksheet. In the actual worksheet, the words being looked for are not always in the same format (i.e. I might be looking for 'Received' and the person entering the data in column 'A' might in one instance enter "12/16/13 - Received", while in another instance "Received Only Part Of Payment") Hence, you can see the need of the Wild Cards.
    Bottom line, you gave me a second solution and probably the one I will use. Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] reference part of cell address from button and part of another cell
    By JJGF in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-24-2012, 04:48 PM
  2. Formula for address of a part of text
    By razwy13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2012, 07:30 AM
  3. Insert cell value as part of address in formula
    By Deutz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2012, 09:02 PM
  4. Replies: 0
    Last Post: 09-04-2009, 06:11 PM
  5. hyperlink with cell value as part of address
    By chevee55 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-09-2006, 03:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1