displaying multiple results from index/match search 1

[DragonRe]

I am using index/match to find something in column B, and then output column C and D.
There are typically multiple matches, this is always finding just the first one.
My data is sorted in alphabetical order descending based on column B. Therefor all my matches will be one after another.

Without using VBA, what is the best way to display all the results.

 

[SkipVought]

Hi,

Check out the OFFSET function. It returns a RANGE. This function has 5 arguments.

1) is a starting point reference
2) is a row offset from 1)
3) is a column offset from 1)
4) is the row depth (number of rows in the return range)
5) is the column width (number of columns in the return range)

Use the MATCH function as arg 2)

Use the CountIF function as arg 4)

FYI faq68-1331 and Excel Help on OFFSET.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DragonRe]

You are a wizard!
It'll take a little time to implement, but this is perfect. I'll let you know how it goes.

 

[DragonRe]

I'm having problems using offset in this application. It will only ever display one result, where I would think it would return an array of cells.

Code EX:
=OFFSET(B:B,MATCH(B1,B:B,0),1,COUNTIF(B:B,B1),1)
which to my understanding means:
go to column B, find where "B1" is, display whats in the next column, display as many items as you find similar to "B1" in column B, display a column width of 1.

instead it displays what is in C2.

 

[SkipVought]

Do not use OFFSET alone.

OFFSET returns an ARRAY.

Use OFFSET as the RANGE for an INDEX or VLOOKUP.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DragonRe]

Could you humor me and give me an example? Whenever I use index with offset I can only get it to display one thing.

 

[SkipVought]

Whenever I use index with offset I can only get it to display one thing. "

How could you display more than one thing? INDEX only displays the value of the intersection of one row and one column.

Give me some sample data in which you have multiple lookup values and tell me what you want returned.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DragonRe]

Ok, having a bit of a miscommunication.
Say I want to have a drop down box of choices of meals to make. Lets say someone chooses "sandwich". I then want excel to display all the ways that "sandwich" could be made. EX:
sandwich beef cheese
turkey bacon
chicken tomato

As it is now, I can only get it to display the first option, in this case: sandwich beef cheese
How can I have it display all the answers? Again, this is *without* using vb.

 

[SkipVought]

I asked you for some sample data like...
[tt]
Item Ingredients
Pie Apple
Pie Cherry
Pie Peach
Sandwich Beef
Sandwich Cheese
Sandwich Turkey
[/tt]
Given a SelectedItem
[tt]
=OFFSET($A$1,MATCH(SelectedItem,A:A,0)-1,1,COUNTIF(A:A,SelectedItem),1)
[/tt]
returns an ARRAY of INGREDIENTS. You use INDEX to display individual items in the array.

So, in my example data, if my selected ingredient were Sandwich, the ARRAY would return 3 elements; Beef, Cheese & Trukey.

You could DISPLAY each of those elements, using the INDEX function. More appropraitely, it can be used as the source for a LIST in Data > Validation, so a user can see a dropdown for this elements in the Ingredients for the SelectedItem.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DragonRe]

Sorry I provided what I wanted as the solution, not what we were starting with. The problem is that the data comes in pairs of information, like in my answer "turkey bacon".

recipe item1 item2
sandwich turkey bacon
sandwich beef cheese
sandwich chicken tomato
burrito beans cheese
etc

So when someone chooses a recipe, I need it to display both items, also it varies how many of each recipe there are, for example, there may be 3 sandwich choices, but only 2 burritos.
In my VB version of this, I have it print them out in cells below until it runs out of matches.
I see this could be done with countif, storing that number in a cell, decrementing the cell as solutions are outputted, and use if statements to have a cell detect when to stop, but is there a more elegant way to do this?

Thanks again for your efforts on this.

 

[SkipVought]

In pairs? WHY? That is a horrible source! What does turkey have to do with bacon, for instance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

What's the purpose here?

You could, (I would not) concatenate the two ingerdient columns into one and the reference that range, but this all does not make any sense, unless I am missing something.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DragonRe]

The information is in pairs, because it's important that it comes in pairs. The turkey bacon thing is just a dummy example, here is a sample of my actual data:
results item1 item2
Abaddon Flame (FIRE) Bomb Core Chocobo Feather
Abaddon Flame (FIRE) Bomb Core Chocobo Wing
Abaddon Flame (FIRE) Bomb Core Dream Powder
Abaddon Flame (FIRE) Bomb Core Farplane Shadow
Abaddon Flame (FIRE) Bomb Core Farplane Wind
Abaddon Flame (FIRE) Bomb Fragment Chocobo Feather
Abaddon Flame (FIRE) Bomb Fragment Chocobo Wing
Abaddon Flame (FIRE) Fire Gem Candle of Life

As you can see there are multiple ways to get the same result, and sometimes item1 is the same.

For vlookup, I concatenate the data, and since you've pointed me to the index/match I've switched to that and also use item1/item2 concatenated to find my result.
Like I mentioned though, I just need a way to display these multiple results. The problem isn't finding them, just displaying them.

 

[SkipVought]

Ahhhhh!!!!

Your example was not an example at all, of your problem!!!

You merely have multiple levels in your table. You attack each level the same way.

Start with a master list of unique value of RESULTS values

With SelectedResult, you get an ARRAY that repersents the CHUNK of data only relates to that result.

From that ARRAY you can get a unique list of Item1 values as a souce for a dropdown, so the user can select a value that would be the SelectedItem. Using the SelectedItem, you gat another array that only relates to elements from that Item.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, use Data > Filter > Advanced Filter - UNIQUE VALUE in another location to generate a unique list of Item1 values realted to Results...
[tt]
results item1
Abaddon Flame (FIRE) Bomb Core
Abaddon Flame (FIRE) Bomb Fragment
Abaddon Flame (FIRE) Fire Gem
[/tt]
That list gives you the offset for your FIRST array. Then your example list...
[tt]
results item1 item2
Abaddon Flame (FIRE) Bomb Core Chocobo Feather
Abaddon Flame (FIRE) Bomb Core Chocobo Wing
Abaddon Flame (FIRE) Bomb Core Dream Powder
Abaddon Flame (FIRE) Bomb Core Farplane Shadow
Abaddon Flame (FIRE) Bomb Core Farplane Wind
Abaddon Flame (FIRE) Bomb Fragment Chocobo Feather
Abaddon Flame (FIRE) Bomb Fragment Chocobo Wing
Abaddon Flame (FIRE) Fire Gem Candle of Life
[/tt]
for the second array to get a list of Item2 values.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DragonRe]

Well I think it was a good example
Plus, I like sandwiches.

It seems like we are not quite on the same page yet.

I have a list of unique results, as well as a list of unique item1/item2 (they happen to be the same).

Here is a posted version of my file. Only of course it doesn't function on the site, which is annoying.

http://spreadsheets.google.com/pub?key=pyRYu0ZK59uEVoczDr0EyZQ&gid=0

I've got this working fully in one direction, choose the 2 items, it will tell you result. Choose the result and it will tell you items to make it, but only the first match it finds.

If you're describing how to do this in your last post I apologize but didn't get that. Selecteditem and Selectedresult do not show up as commands in excel help, and I believe you are just referring to those as field descriptions.

 

[SkipVought]

Selecteditem and Selectedresult do not show up as commands in excel help"

They are not. I use Named Ranges in order to CLARIFY my range references. Your selected Item may reside in AA952. How descriptive is that?

I am denied access to your remote posting, by company policy.

Please copy and post HERE.

"I've got this working fully in one direction, choose the 2 items, it will tell you result. Choose the result and it will tell you items to make it, but only the first match it finds."

Well, YES, if you use INDEX. I've told you that I would be using Data > Validation -- LIST (3 of them). That way you can dripp down, using TWO validation lists, and see the final list in the third.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Really, seeing your current example and what you want as a result, you'd be better off using MS Query to drill down.

faq68-5829.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]