+ Reply to Thread
Results 1 to 5 of 5

Look up in multiple columns

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    Richmond
    MS-Off Ver
    Excel 2007
    Posts
    22

    Look up in multiple columns

    Hi There -

    I am trying to lookup a code in a table of data and return a description from a particluar column of the table. The code could be in any number of columns. Plse see attached spreadsheet.

    Any help would be very gratefully received.

    Thank you

    Tom
    Attached Files Attached Files

  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: Look up in multiple columns

    Put this formula in G8:

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


    and copy down. Then you can use this formula in J8:

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


    You can copy this across to K8, then copy J8:K8 down.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Look up in multiple columns

    I have tried without helper columns.
    Attached Files Attached Files
    Please click 'Add reputation', if my answer helped you.

  4. #4
    Registered User
    Join Date
    04-08-2013
    Location
    Richmond
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Look up in multiple columns

    Hi Pete - I can't use the concat because in the real version of the spreadhsheet the other empty cells are filled with other codes. I have updated the file to show the issue.

    So really need to look up the value across several columns of filled data

  5. #5
    Registered User
    Join Date
    04-08-2013
    Location
    Richmond
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Look up in multiple columns

    I can't use the concatination because in the real version of the spreadhsheet the other empty cells are filled with other codes.

    So really need to look up the value across several columns of filled data

    I have updated the file to show the issue.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-16-2013
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    34

    Thumbs up Re: Look up in multiple columns

    try this one also copy and paste or drag in desire cell..

    =IFERROR(INDEX($E$8:$F$11,SMALL(IF($B8:$D8=$I8,ROWS($B$8:D8)),1),MATCH(L$7,$E$7:$F$7,0)),"Not Found")

    pls use CSE control+shift+enter because this is an array formula...........
    Last edited by ashishraj; 09-06-2013 at 03:09 AM.

+ 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. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  2. Replies: 1
    Last Post: 07-22-2013, 07:08 AM
  3. [SOLVED] Sum Multiple Columns if successful criteria are matched in multiple columns
    By dharmeshchavda in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2013, 11:02 PM
  4. Multiple rows with assoicated columns to multiple columns
    By fergiepl7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2013, 03:34 AM
  5. Return Multiple Values with multiple rows & multiple columns
    By sachin parab in forum Excel General
    Replies: 2
    Last Post: 11-05-2012, 10:21 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