+ Reply to Thread
Results 1 to 5 of 5

Getting data for a particular cell from different columns

  1. #1
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Getting data for a particular cell from different columns

    Dear All,
    I need to get data for a particular cell from same column (Multiple vlookup) and from different columns. For example I have person 1 to 10 who are assigned to different projects. Now I have data project wise. I need the data person wise. So in my first column there are name of the companies and then in the next columns (Which go till BA in the original sheet) there are persons names. I can get the names from one column using index and if and small (Which i learnt in this forum some years agao), however I am not able to get the names from different columns. I have attached the sheet to show my problem and expected solution. Please let me know if you need more information.
    Attached Files Attached Files
    Last edited by inayat; 03-17-2020 at 09:03 AM. Reason: Solved
    One must rise above the Clouds to see the Blue Sky rather than constantly trying to push them aside

    If you want to say Thank you to a member, click the little star * below.

    If your Problem is Solved, please mark the Thread as Solved

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,512

    Re: Getting data for a particular cell from different columns

    Into D18:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    as it is array formula you have to accept it with Ctrl+Shift+Enter.
    Then drag it right and down.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,088

    Re: Getting data for a particular cell from different columns

    Please try at D18
    =IFERROR(INDEX($C$4:$C$13,SMALL(IF(($D$4:$E$13=$C18),ROW($C$4:$C$13)),COLUMNS($D18:D18))-ROW($C$3)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Getting data for a particular cell from different columns

    Thank you. Both of them worked. However I have one question for @KOKOSEK. What does 10^10 do??? That is the only part I am not able to understand. I will then mark the thread as solved.

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,512

    Re: Getting data for a particular cell from different columns

    Bacuse we want to deal with SMALL function whatever is out of our criteria ($D$4:$E$13<>$C18) gets huge value (10^10) to go out of range of col. C.

+ 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. Getting data from different columns in a same cell
    By Amassey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2017, 06:01 AM
  2. [SOLVED] Comparing Data in a cell to data in multiple columns
    By Joym in forum Excel General
    Replies: 8
    Last Post: 02-01-2017, 09:57 AM
  3. Selecting cell 2 columns over but the first cell for data entry
    By frankthefro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2015, 01:33 PM
  4. macro that copies data in cell and paste it to cell x columns to right in shhet
    By Stevebarnes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2013, 12:14 PM
  5. [SOLVED] Grouped Data Blocks editted to Columns of Data based off left cell value?
    By BlazzedTroll in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 06-10-2013, 03:06 PM
  6. Populate 2 columns with data from a different two columns based on one cell's value
    By ChrisHowk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2012, 06:01 PM
  7. Breaking Cell Data Into Columns
    By OchoLoco in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2012, 03:58 PM

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