Index Match with multiple results

[pitterpatton]

I am having trouble with this formula: =index(sheet1!B1:B30, match(sheet2!D1,sheet1!A1:A30,0)), this works great but I have multiple results that I would like to be listed in separate rows. My question is if say there are two or more rows that say boy or girl but column 2 has a different number is there a way to get each one to list in different rows. So say:
COL 1 COL 2
Row 1: Boy 100
Row 2: Girl 200
Row 3: Dog 300
Row 4: Cat 400
Row 5: Boy 200
Row 6: Girl 400

With the original formula in "F1" and have the multiples display in say "F2", "F3", "F4"..., without getting duplicates? I hope this makes sense, thank you very much if you any way help with this!

Previous forum that I got the original formula from.
http://www.excelforum.com/excel-form...e-options.html

[azumi]

You mean like this?

[pitterpatton]

*I cannot seem to download an attachment to give you this spreadsheet? Sorry I am new to this but I do not see the paperclip to attach anything?*
I am using the sheet "Receipt" and columns 17-31 for the information needed. I am getting the information from "Sale Sheet", but as you can see I have multiple lot #'s in column A being bought by the same buyer # in column D. I need to figure out how to get all the Lot #'s bought by say buyer # 101 so show up on my rows 17-31 on the "Receipt" sheet. Hope this explains better!

[pitterpatton]

Index.xlsx
Attachment I was talking about on previous post.
Hope this works!

[FDibbins]

1st, unmerge those cells and remove unneeded columns. Then try this ARRAY formula in B17, copied down and (carefully) across...

=IFERROR(INDEX('Sale Sheet'!A$2:A$19,SMALL(IF('Sale Sheet'!$D$2:$D$19=$C$11,ROW($A$1:$A$18)-ROW($A$1)+1),ROWS($A$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[pitterpatton]

Thank you very much it worked perfectly, I really appreciate you spending the time to help me figure this out!

[FDibbins]

Happy to help and thanks for the feedback

[pitterpatton]

Index.xlsx
As I started playing with this formula more I am having a few issues that I cannot seem to fix. Sorry some of this is getting way over my head! But when I put more items in on "Sale Sheet" down to row 69 when I try putting a buyer # in C11 now nothing comes up. I did change the formula a little to be =IFERROR(INDEX('Sale Sheet'!$A$2:$A$69,SMALL(IF('Sale Sheet'!$D$2:$D$69=$C$11,ROW($A$1:$A$18)-ROW($A$1)+1),ROWS($A$1:A1))),""). Because I needed it to go all the way to row 69, did I mess something up by doing that I attached a new worksheet to see if someone can figure out what I need to change.

Thank you so much for all the help!

[FDibbins]

wow great job, you were really close You just left out adjusting the last part - understandable as it starts in row 1 and ends in row 18, doesnt quite look the same...

=IFERROR(INDEX('Sale Sheet'!$A$2:$A$69,SMALL(IF('Sale Sheet'!$D$2:$D$69=$C$11,ROW($A$1:$A$68)-ROW($A$1)+1),ROWS($A$1:A1))),"")
Again ARRAY entered.

If you intend extending that table more, a quick way to do that so you dont need to mess with the formulas, is to add rows just abobve the last row - the formula will adjust itself

[Tony Valko]

If you index the entire column then you don't have to mess with an offset correction.

=IFERROR(INDEX('Sale Sheet'!$A:$A,SMALL(IF('Sale Sheet'!$D$2:$D$69=$C$11,ROW('Sale Sheet'!$D$2:$D$69)),ROWS($A$1:A1))),"")

[pitterpatton]

They both worked great thank you very much for your hard work to help me on this I really do appreciate it!!!

[Tony Valko]

You're welcome. We appreciate the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.