Multiple Array Search

[Ian-p]

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 ?

 

[Khalid NGO]

Hi Ian,
Welcome to the forum...

See the attached file and comment.

Regards,

 

[Ian-p]

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

 

[NARAYANK991]

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

 

[prasaddn]

Hope this will solve:

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

See the attached file.

Regards,
Prasad DN

 

[Ian-p]

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

 

[Khalid NGO]

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

Sorry, not much cleared in your first post.