Lookup value based on series of options

[chinrub]

Hello
First I apologize if my title is a little misleading as I'm struggling to think of a simple way to describe what I want to do.

My problem is I have a number of numbers that can be attributed to any one name. I'm retrieving these numbers from an external source and so am looking for a way to look up a name based on a collection of numbers pretty much automatically.
The numbers will be stored in a second sheet and will most likely be unsorted if that makes a difference.

I'm attaching a dummy workbook which probably explains what I want to achieve better than I can describe.

Thanks for reading and for any help anyone can offer.

[benishiryo]

hi chinrub, welcome to the forum. it would be good to show the desired results you want too. but i'm guessing in B3:
=IF(COUNTIF(Sheet2!C3:L3,C3),Sheet2!B3,"")

[chinrub]

such a quick response, and thanks for the welcome. I'll try it now.

Edit - Wow! Such a simple solution too. I'm glad I came here. I went off on a tangent looking into Index and Match functions which I was struggling to get my head around.
This seems to do what I want, I'll test it out on my real data later and will most definitely be back for more advice should I need it.

Edit 2 - I've hit a bit of a problem again. Probably my fault for not explaining this aspect of my design.
The list of names in sheet one will not appear in the same order as it does in sheet two. The function you posted would work beautifully if it did. However I have no control over that. Are there any methods you know of that can take this into account. I believe the "Sheet2!B3" from

Please Login or Register  to view this content.

needs to be something that will return the name from the same row it finds the result.

[chinrub]

Just updating the situation. I found this very old post helpful.
http://www.excelforum.com/excel-prog...der-value.html

I'm not sure how excel has changed over the years or if there's a better way of doing what I wanted but I managed to apply what I found. With a minor tweak to how I was collecting the names and numbers it all works well.

Thanks again for your response benirshiryo.

I'm attaching my dummy file in case it's of any use to anyone. Just change the number, which in my actual sheet is provided via microsoft query and the relative name should be matched and changed accordingly.

Now how do I mark this solved

[benishiryo]

sorry i didn't see your updated post. if you edit a post, we can't see any notification. you can do that, but PM me about it. as for your formula, you should use this to apply to your scenario:
=INDEX(Sheet2!$B$3:$B$5,SUMPRODUCT((Sheet2!$C$3:$L$5=C3)*(ROW(Sheet2!$C$3:$L$5)-ROW(Sheet2!$C$3)+1)))

if there's a case where values are not found, it would give you a wrong answer. so if there's such a need, try:

Please Login or Register  to view this content.

my signature guides you on how to mark as "Solved" too

[chinrub]

Thanks again benishiryo I'll mark this as solved