• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Array Search

Ian-p

New Member
Hi - I've tried various ways with no success to solve the following XL problem. I've created a simple worksheet to simplify my issue. I've attempted to upload the spreadsheet, but it fails :( so I've provided in this text:

Name Middle Middle Middle
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4


Middle C3 Name

What I have is a table of unique middle names, which happen to correspond to their individual cells, and a name. I want to enter the Middle Name (C3 in my example), and return the of Name (A3 in this example). I've had numerous attempts at INDEX MATCH but to no avail, and all the examples I've read seem to be about searching multiple columns only of different values. Any thoughts ?
 
Hi Ian,
Welcome to the forum...

See the attached file and comment.

Regards,
 

Attachments

  • Index-Match.xlsx
    8.6 KB · Views: 2
Sorry, that's no good & you've missed my point. You're only looking in Col C, but I need to look in cols B,C & D i.e if I change the search name to B3, the result is #N/A
 
Hi ,

It would help if you could upload your workbook with proper data ; use any public file-sharing website such as GoogleDocs , DropBox , SkyDrive ,... and after giving others permission to access and open your file , post the link here in this same thread.

Narayan
 
Hope this will solve:

=INDEX(A1:A4,SUMPRODUCT((B2:D4=C8)*ROW(B2:D4)))

See the attached file.

Regards,
Prasad DN
 

Attachments

  • Index-Match(1).xlsx
    9.4 KB · Views: 3
Thanks - That's exactly what I was trying to do, and been so close on several occasions, just a misplaced bracket was all I had wrong at 1 point :oops:
 
Back
Top