+ Reply to Thread
Results 1 to 11 of 11

Look up range, return to other columns

  1. #1
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    283

    Look up range, return to other columns

    I wonder if anyone could give me a formula to look up the range in C and return the data in B to N & O as attached please. :-) T.I.A.
    I am missing something here?
    Attached Files Attached Files
    Last edited by And180y; Today at 01:50 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,625

    Re: Look up range, return to other columns

    Hi

    In N1
    =TRANSPOSE((UNIQUE(C2:C4,FALSE)))

    In N2 copied to O2
    =FILTER($B$2:$B$4,$C$2:$C$4=G2)

    However this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy) so that we can consider a real world question. It strikes me that a Pivot Table may be a better option.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    684

    Re: Look up range, return to other columns

    with N1="bb", do you want to find "hello" in O1 or N2 ?
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  4. #4
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    283

    Re: Look up range, return to other columns

    Hi bsalv, thanks for reply so every then with gb would appear in column N and with bb would be in column O.

  5. #5
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    283

    Re: Look up range, return to other columns

    Posted in error
    Last edited by And180y; 11-25-2021 at 10:13 PM.

  6. #6
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    283

    Re: Look up range, return to other columns

    Richard, thankyou so much it works!! For future reference how would I add for example column A to the formula?

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,462

    Re: Look up range, return to other columns

    What would be in Column A? Whatever you put in Column A, change the reference in the current formula of where it is to Column A. Maybe I'm misunderstanding your question.

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,625

    Re: Look up range, return to other columns

    Quote Originally Posted by And180y View Post
    Richard, thankyou so much it works!! For future reference how would I add for example column A to the formula?
    How would you expect the output to look? At the moment you essentially have a 2 dimensional range which you want to transpose with unique items in a column resulting as unique items in a row, and underneath the result columns you list the column B items. IT's not clear how a 3rd column A is to be shown

    I need to see the result. Please add and reupload.

  9. #9
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    283

    Re: Look up range, return to other columns

    Hi Richard,
    sorry for delay. Using Filter wont work on my laptop.
    I have tried as per the attached sheet to use Vlookup and seperately Index Match
    but must be missing something.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,657

    Re: Look up range, return to other columns

    In I2

    =IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,ROW($A$1:$A$100)/($A$2:$A$15=$I$1),ROWS($1:1))),"")

    in J2

    =IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,ROW($A$1:$A$100)/($A$2:$A$15=$j$1),ROWS($1:1))),"")

    copy both down
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,625

    Re: Look up range, return to other columns

    Quote Originally Posted by And180y View Post
    Hi Richard,
    sorry for delay. Using Filter wont work on my laptop.
    If you have 365 as your profile shows then there's no reason why the new SPILL functions like FILTER won't work.

+ 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. Return the number of columns with value within a custom range
    By 7empest in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2021, 05:29 AM
  2. Look up across columns by Date Range and Return multiple matches
    By alehouse1971 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-26-2020, 09:55 AM
  3. Replies: 4
    Last Post: 10-21-2019, 06:34 PM
  4. Replies: 17
    Last Post: 03-08-2018, 05:55 AM
  5. [SOLVED] Return percentage based on range of cells from two columns
    By Lillerpool in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-15-2015, 08:27 AM
  6. Replies: 5
    Last Post: 03-04-2014, 06:09 PM
  7. If value in data range (multiple columns) return row
    By flickflick in forum Excel General
    Replies: 0
    Last Post: 08-18-2006, 06:59 AM

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