+ Reply to Thread
Results 1 to 8 of 8

Search a sheet for a song title and return Album title

  1. #1
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Search a sheet for a song title and return Album title

    I have a workbook containing my cd collection, I would like to search for a song title to find the album title of the cd it appears on and return the result on sheet1

    My cd's are listed 1 cd per column on sheet2
    Cd titles are in row 2 on sheet2

    The cell to search from is on sheet1

    Any help is much appreciated

  2. #2
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    Re: Search a sheet for a song title and return Album title

    please attach the file so that someone can help you out.
    Ash

  3. #3
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Search a sheet for a song title and return Album title

    File attached ...I think
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,930

    Re: Search a sheet for a song title and return Album title

    Try

    =INDEX(Sheet2!$A$1:$BN$1,SUMPRODUCT((Sheet2!$A$4:$BN$100=Sheet1!$B$3)*(COLUMN($A$4:$BN$4))))

    If there is a track by more than one artist then it will return first CD found.

    I extended the ranges (Row/columns) for future additions

  5. #5
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Search a sheet for a song title and return Album title

    Many thanks. JohnTopley Just the ticket.

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,745

    Re: Search a sheet for a song title and return Album title

    Or try this ...

    =INDEX(Sheet2!$A$1:$Z$1,MAX(IF(Sheet2!$B$2:$Z$100=B3,COLUMN($B$2:$Z$100),"")))

    Enter with Ctrl+Shift+Enter.

  7. #7
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Search a sheet for a song title and return Album title

    "If there is a track by more than one artist then it will return first CD found."
    Is there a way to get around this issue?

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,930

    Re: Search a sheet for a song title and return Album title

    Try

    in B5


    =IFERROR(INDEX(Sheet2!$A$1:$Z$1,SMALL(IF(Sheet2!$B$2:$Z$100=$B$3,COLUMN($B$2:$Z$100),""),ROWS($1:1))),"")

    Enter with Ctrl+Shift+Enter

    Drag down until you get a blank

+ 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] Show full path title in title bar?
    By Nor in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 04-11-2014, 02:26 PM
  2. Need to search array and return value along with title and row values
    By wilburr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-28-2013, 06:01 AM
  3. Using cell contents to search for corresponding sheet title
    By teblowtime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-30-2013, 03:31 PM
  4. Replies: 12
    Last Post: 08-10-2012, 05:11 PM
  5. The Song Title Game
    By ratcat in forum The Water Cooler
    Replies: 440
    Last Post: 07-23-2009, 07:52 AM
  6. Replies: 2
    Last Post: 07-21-2006, 05:10 AM
  7. [SOLVED] [SOLVED] how to use a music icon and listen to part of a song title
    By music icons in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-28-2005, 10:05 PM
  8. Pasting Objects into Chart title and Axis title
    By Sam in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-06-2005, 04:05 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