+ Reply to Thread
Results 1 to 13 of 13

Vlookup or If/Then to return a substitute cell for a cell that matches itself in a range

  1. #1
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Vlookup or If/Then to return a substitute cell for a cell that matches itself in a range

    I know the title might seem confusing. This is what I'm trying to do.

    I have a list of numbers in column A. Everything to the right of column A in the same row can be represented by the A cell. So A2:A40 are to be represented by A1, etc. I have another sheet where I have the list of all possible numbers. I want to run a formula that will return the value in the A1 cell for all numbers that have matches in A row. So in my other sheet, if A2 comes up, in the column next to it, I want A1 to appear. I'll attach a book to show what I mean. There are two tabs.

    Thanks.
    Attached Files Attached Files
    Last edited by Shadefalcon; 11-13-2012 at 03:33 PM.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Shadefalcon,

    Attached is a modified version of your posted workbook.
    The items in sheet 'Table 1' are now all the results of formulas.

    In cell B2:
    Please Login or Register  to view this content.

    In cell B3 and copied down to B16 (note that B16 is blank becuase the table's data has been exhausted):
    Please Login or Register  to view this content.

    In cell A2 and copied down:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Now, can I apply this to a different book, with ranges of different sizes? I just used this small book as an example. My actual book is 249 rows deep and around 150 columns wide.

  4. #4
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Also, some of the rows in the other book don't have anything next to them in columns, all they have is a value in the A column, but I still want them to be accounted for in the other table.

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    It should work with both of those conditions. Just update the appropriate range references

  6. #6
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    The other issue is, in my other book, the first cell in Table 1 doesn't match the first cell in Table 2. Not all of them are in Table 2 either, I just want to the ones that do match to be returned to the second column in the first Table. Does that make sense? There are 10000 rows in Table 1, and just shy of 300 in Table 2.

  7. #7
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    The range of Table 1, including column headings, is A1:B10598, and Table 2, including column headings, is A1:CL295

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Was your example file representative of your actual workbook? If so, you should be able to adapt the formulas. Otherwise, I'd have to see an example file that is actually representative of the workbook.

  9. #9
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    This might be a better example. See how not everything has a match and not everything is in order? So for ones where there isnt a match from the second table, Nothing should show up in column B in the first table. Does that work?
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Way too late and not even a formula but hey! Here goes nuthin...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  11. #11
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Shadefalcon,

    Attached is a modified version of example book 2.

    In sheet 'Table 1' cell B2 and copied down is this array formula:
    Please Login or Register  to view this content.
    Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how they get the curly braces {}. Do not try to add those yourself.

  12. #12
    Registered User
    Join Date
    09-26-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    MS Office 2010
    Posts
    64

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    Yep. That worked. Thanks tigeravatar. Another job well done.

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

    That's like a reversed VLOOKUP, how neat!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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