# Search a sheet for a song title and return Album title

1. ## 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. ## Re: Search a sheet for a song title and return Album title

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

3. ## Re: Search a sheet for a song title and return Album title

File attached ...I think

4. ## 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. ## Re: Search a sheet for a song title and return Album title

Many thanks. JohnTopley Just the ticket.

6. ## 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. ## 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. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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