Formula to Pull Data from Table

Hi Friends

I want to create a formula in the attached worksheet. I want to create a formula in column from A6 to A20 & go on.

The formula should look up Cell B2, & If B2 match the data in Column I to M, then it should list all the data as per the table.

Can anyone please help what's the best formula as Vlookup doesn't help here?

Thanks in advance

Regards
Arpan

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

B6:

Formula:

=INDEX(K$2:K$39,MATCH($B$2&$A6,$I$2:$I$39&$J$2:$J$39,0))

committed with Ctrl-Shift-Enter rather than just Enter as this is an Array Formula


Regards, TMS

@Ford: sorry

Originally Posted by TMS

@Ford: sorry

Timing issue, no problem

Try this one
in A6 and pull formula across and then down

=IFERROR(INDEX(J:J,SMALL(IF($I$2:$I$39=$B$2,ROW($I$2:$I$39)),ROWS(I$2:I2))),"")

...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.

When I copy the formula & paste in the cell A6, it give me 0.

Did you press Ctrl-Shift-Enter?

Is there way to use without Array?

Probably. But I don't know what it is off the top of my head.

Thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

Originally Posted by arpanpanchal

Hi Alkey

Can I use without array formula? I have used CTRL+D, the formula is correct, but doesn't bring the correct answer. Is there any reason for array? Any possibilities without array?

Thanks & Regards
Arpan

Unfortunately, for complex data extraction array formulas is the way to go. If I new of an easier method I would certainly offered it. For those who use Excel version 2010 and there is an alternative but is not available in version 2007.

I am using Excel 2010.

Suggest you update your profile.

You have to use Ctrl-Shift-Enter to commit the Array Formula, at least in the first cell. You can then use Ctrl-D to Fill Down ... although that is a shortcut key I have never used (probably because you have to select the cells to fill into, whereas there are other methods of filling down that don't require pre-selection.

Yes, this also an array formula but it doesn't require Ctrl+Shift+Enter

in A6 and pull formula across and then down

=IFERROR(INDEX(J:J,AGGREGATE(15,6,ROW($I$2:$I$39)/($I$2:$I$39=$B$2),ROWS(J$2:J2))),"")

You're welcome. Thanks for the rep.


Must start playing more with my copy of 2010