Extracting Data from list

[ExcelRoy]

Hi all,

I am trying to extract data from a list using formula but cannot seem to get it right

B10 has a name

cell with formula B13:B103

I am looking to extract data from M13:M103 when the list matches B10 in Q13:Q103

Then drag down to extract every entry etc

Any help much appreciated

 

[Fluff]

How about
=IFERROR(INDEX($M$13:$M$103,AGGREGATE(15,6,(ROW($M$13:$M103)-ROW($M$13)+1)/($Q$13:$Q$103=$B$10),ROWS($A$1:A1))),"")

 

[ExcelRoy]

Hi Fluff,

Many thanks, does this work with 2007?

Thanks

 

[Fluff]

No it doesn't, try
=INDEX($M$13:$M$103,SMALL(IF($Q$13:$Q$103=$B$10,ROW($Q$13:$Q$103)-ROW($Q$13)+1),ROWS($A$1:A1)))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, rather than just enter

 

[ExcelRoy]

and Boom!

That's perfect thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback

 

[ExcelRoy]

Hi Fluff,

Would there be a reason why this formula wouldn't pick up the data from a RANK and VLOOKUP table?

Thanks

 

[Fluff]

Why?
What is the formula returning?
Also do you have any formulaic errors in Q13:Q103 such as #VALUE!, #N/A etc

 

[ExcelRoy]

Hi Fluff,

Its returning "" but yes they do have #VALUE

No probs I got it from here

Thanks again

 

[Fluff]

You're welcome