Trying to get a Lookup Function to retrieve Multiple results... proving hard....

[BobaJFett]

Ok so this is nerdy but I don't care, I am making a excel sheet to help select pokemon for my party. I have most of the info in the thing but I am trying to create a situation where I can type "water" into a cell and have a list of the names of the types of pokemon its good against placed in the cells beside it. I'm sure this has been solved before, but I have been trying for the past 2 hours now, and all I keep finding are "Use Index". I am having some trouble with adapting that to what I have here.

Anyway, all help is appreciated, thanks

[ajryan88]

Hi,

I know a pretty complex INDEX/MATCH formula that can probably achieve the desired result. However, I'm going to need more context around where you want the formula.

Firstly, where will you enter "water"? (which cell on which sheet)
Secondly, where should the formula be that displays the list of Pokemon? (which cell on which sheet)
Thirdly, please give an example of which Pokemon should be in the list if you enter "water", so that I can verify the accuracy of my own formula before submitting it to you.

Thanks

[ajryan88]

I've had a go at what I think you're asking for. I've inserted the list on the "Party" worksheet. The formula returns each type that scores a 2 along the row represented by Type 1, as per the matrix on the "Type Chart" worksheet.

Let me know if this is what you're after?

[BobaJFett]

Hi,

I know a pretty complex INDEX/MATCH formula that can probably achieve the desired result. However, I'm going to need more context around where you want the formula.

Firstly, where will you enter "water"? (which cell on which sheet)
Secondly, where should the formula be that displays the list of Pokemon? (which cell on which sheet)
Thirdly, please give an example of which Pokemon should be in the list if you enter "water", so that I can verify the accuracy of my own formula before submitting it to you.

Thanks

Hi, thanks for replying so quick.

So I want to have it list the Pokemon Water is super effective against in the "party" sheet, beside "Dark". I have it so that it automatically loads what type of pokemon are in my party, so I was hoping that a list from the data in the "Type Chart" sheet would work. The "type chart" works with the attacking pokemon on the left and defending up top. Water is super effective against ground, rock and fire. If those could come up beside "dark" that would be awesome.

This has made me realize I will have to rethink my layout in the "party" sheet....

[ajryan88]

Excellent, I think I guessed it correctly then. Let me know how the attachment works for you

[BobaJFett]

I've had a go at what I think you're asking for. I've inserted the list on the "Party" worksheet. The formula returns each type that scores a 2 along the row represented by Type 1, as per the matrix on the "Type Chart" worksheet.

Let me know if this is what you're after?

Sorry man thanks, checking it out now!

[BobaJFett]

Sorry man thanks, checking it out now!

Man Thats AWESOME!!! Thanks!!!

[ajryan88]

No problems at all

Please don't forget to mark this thread as solved (Thread Tools --> Mark as Solved), and please click on the * next to my post to say thanks