+ Reply to Thread
Results 1 to 2 of 2

Inserting a formula for cell range within another formula

  1. #1
    Registered User
    Join Date
    06-13-2019
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    1

    Unhappy Inserting a formula for cell range within another formula

    Good afternoon Excel Forum,

    I have been struggling with this problem for over a week now and have not been able to figure it out. What I have is a data set that i need to paste into an excel on a frequent basis. The downside to the way this is pasted is that things may not always be in the same place when i paste it. I have been using VLOOKUP to accomplish what i am asking. Here is where the complicated part comes in. This pasted data sometimes has multiple columns. What i am trying to do is have a formula that finds the 2nd column in the data set and starts the range for a VLOOKUP based on where this column is located.

    Here is what i have been able to accomplish so far.

    This equation finds the 2nd column's start cell within the pasting range.

    =ADDRESS(55+(MATCH("Red Tagged",'sheet2'!M55:M77,0)),13)

    The name of the second column in the pasting range is "Red Tagged". This equation output the cell that 'Red Tagged' appears in the pasting range. In this case it is $M$69.

    Now i want this cell be to be used as the first cell in the range of a VLOOKUP equation. Here is what my VLOOKUP equation currently looks like:

    =IFERROR(VLOOKUP("Combos",'sheet2'!M55:N77,2,0),"N/A")

    This is how i want the equation to look and work:

    =IFERROR(VLOOKUP("Combos",'sheet2'!(ADDRESS(55+(MATCH("Red Tagged",'sheet2'!M55:M77,0)),13)):N77,2,0),"N/A")

    But unfortunately the equation does not work like this. It appears that it wont let me have an equation for a cell location within a equation.


    Any help or idea of how to make something like this work would be greatly appreciated!

    Thanks for taking a look!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Inserting a formula for cell range within another formula

    You need to use the INDIRECT function to convert what is essentially a string of text which represents some range reference into the actual range reference so that Excel can act upon it.

    Alternatively, you could use INDEX to generate that range, in which case you would not need the ADDRESS function.

    If you are not sure how to use these functions, then it would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

+ 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] Inserting array formula for dynamic range via VBA
    By pnnaik in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2015, 02:05 PM
  2. Need formula using 'If' condition & inserting a specific range.
    By andyzz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-26-2014, 02:58 AM
  3. [SOLVED] Looping through a range and inserting a formula where condition met
    By strud in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 06:24 AM
  4. Inserting formula into cell/range refering to a range
    By CobertRannon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-30-2013, 01:28 PM
  5. Inserting a range or an array into a sumifs formula
    By mike_vr in forum Excel General
    Replies: 1
    Last Post: 11-03-2011, 11:34 AM
  6. Inserting simple formula in range
    By sivanvega in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2010, 01:31 AM
  7. Inserting a formula into a range of cells using VBA
    By NBVC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2007, 11:11 AM

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