INDEX and MATCH was fine...but

[ArbieBrown]

Long time lurker - now needing help!

Basically I have the following formula:

=INDEX('Data Drop'!$A$1:$N$1000,MATCH($B2,'Data Drop'!$B1:$B1000,0),0)

Which is very good at looking up the requisite information and bringing it back (I have a bunch of them on a sheet which then matches column B's data with the indexed info, to collect the correct output)

It all works great - and is a massive timesaver on a repetitive report.

Or at least it would be

Until I realised that some of the data in Col B now should/could match multiple results.

I cannot for the life of me work out how to convert this effective formula into one that can return multiple results and still work...

Any help - or even some pointers - would be gratefully received!

 

[Scott T]

This shows how to return more then one record with index.

https://youtu.be/SCiqQiC7Bwk

 

[ArbieBrown]

This shows how to return more then one record with index.

https://youtu.be/SCiqQiC7Bwk

Thanks Scott, but that returns Max values from multiple records - not close to what I'm trying to do which is to return the cell(s) that corresponds to multiple records which means that I capture the information for all the duplicates.

Interesting though - bound to have a use for Max values at some stage!

 

[Scott T]

Just adjust the formula to meet your criteria. something like this. I assume you are copying across if you are going down then change the columns to rows

=IF(COLUMNS($A2:A2)>COUNTIF('Data Drop'!$B$2:$B$8,$B2),"",INDEX('Data Drop'!$A$2:$A$8,SMALL(IF(Sheet4!$B2='Data Drop'!$B$2:$B$8,ROW('Data Drop'!$B$2:$B$8)-ROW('Data Drop'!$B$2)+1),COLUMNS($A2:A2))))

 

[ArbieBrown]

Cheers Scott - but I still cannot get that to work!

Maybe I am missing something obvious, but I cannot seem to spot what it is.

I've uploaded the file here: https://drive.google.com/file/d/0B-Rkd3iYH8zWRG9jeEpzWGMycjA/view?usp=sharing In it's original form - if you find you are at a loose end I would welcome your view!

 

[Scott T]

I can not download files at work. Like the formula in the video this is an array formula and needs CONTROL+SHIFT+ENTER. I will try to take a look after work.

 

[ArbieBrown]

Only if it is not too much trouble!

And yeah - I knew about CTRL+SHIFT+ENTER - it's not that.

Really appreciate your attention.

 

[Scott T]

Listing the values in column B and using it to lookup data is not the best way to do this since you may or may not have duplicates. I think the best way to do this is to have a helper column in your Data Drop sheet that you use to pull the data. So in Data Drop you add a helper column that looks up the name based on column B and then in your other sheet do your Index pulling based on the helper column Or maybe use VBA.

 

[ArbieBrown]

Given all the effort that you made I re evaluated what I was doing and used helper columns to push some of the calculations elsewhere to be then used in the appropriate space.

Works great - but I wouldn't have gotten there without your help!

Many thanks

 

[Scott T]

Glad to help.