Lookup, index, match :-(

[crackod93]

sorry for creating a 2nd thread, looks like the original has been lost in the forum and i still really need help please

ive created a sample excel for this which can be found here which kind of shows what i want to do

https://dl.dropboxusercontent.com/u/...85/sample.xlsx

source tab has the data source
output tab has the desired output

im trying the find the first occurrence of X12 on Source tab, then where the row under that column contains a 1, pull out the Name_## and show all Name_## where this is true for every single row where 1 is found under that column

thanks again!

 

[StefanVL]

sorry for creating a 2nd thread, looks like the original has been lost in the forum and i still really need help please

ive created a sample excel for this which can be found here which kind of shows what i want to do

https://dl.dropboxusercontent.com/u/...85/sample.xlsx

source tab has the data source
output tab has the desired output

im trying the find the first occurrence of X12 on Source tab, then where the row under that column contains a 1, pull out the Name_## and show all Name_## where this is true for every single row where 1 is found under that column

thanks again!

Link seems to be broken

 

[Special-K99]

It's this one isn't it?

http://www.mrexcel.com/forum/excel-questions/806996-index-match-vlookup.html#post3946219

To find threads you've posted click on your name, then on View Forum Posts

 

[crackod93]

sorry, something went wrong - this should work https://dl.dropboxusercontent.com/u/59255085/sample.xlsx

 

[crackod93]

the ...85 part of the url should be replaced with this

59255085

looks like the forum doesnt allow this

 

[StefanVL]

There sure is a faster way to do this other than this but this is my go at it:

=IFERROR(INDEX($A:$A;SMALL(IF(SUMIF($A$2:$A$27;$A$2:$A$27;OFFSET($A$2:$A$27;0;MATCH(R$15;$B$1:$M$1;0);ROWS($A$2:$A$27);SUM(IF($B$1:$M$1=R$15;1;0))))=0;"";ROW($A$2:$A$27));ROWS($R$16:R16)));"")

enter with ctrl+shift+enter

I made this one the same tab as the input by the way.

 

[spoon_db]

Array formula, start from row 2...

=INDEX($A$2:$A$27;SMALL(IF($B$2:$B$27>0;ROW($B$2:$B$27)-1);ROW()-1))

 

[crackod93]

There sure is a faster way to do this other than this but this is my go at it:

=IFERROR(INDEX($A:$A;SMALL(IF(SUMIF($A$2:$A$27;$A$2:$A$27;OFFSET($A$2:$A$27;0;MATCH(R$15;$B$1:$M$1;0);ROWS($A$2:$A$27);SUM(IF($B$1:$M$1=R$15;1;0))))=0;"";ROW($A$2:$A$27));ROWS($R$16:R16)));"")

enter with ctrl+shift+enter

I made this one the same tab as the input by the way.

I managed to get this one working (unfortunately not the array one), but what is the reference to R16 for please???

 

[StefanVL]

I managed to get this one working (unfortunately not the array one), but what is the reference to R16 for please???

The R16 works for the second part of the small formula. The first part gives an array of every row that has the correct name, which means row 11, 13, 14 and 17 in the array. By using rows(r16:r16) u can automatically choose the first smallest, the second smallest, etc.

You can also try this by just putting in =ROWS($R$16:R16) hovering over the formula and pressing F9. You'll then see 1 in the first cell, 2 in the second and so on.

 

[crackod93]

The R16 works for the second part of the small formula. The first part gives an array of every row that has the correct name, which means row 11, 13, 14 and 17 in the array. By using rows(r16:r16) u can automatically choose the first smallest, the second smallest, etc.

You can also try this by just putting in =ROWS($R$16:R16) hovering over the formula and pressing F9. You'll then see 1 in the first cell, 2 in the second and so on.

awesome, thank you very much and to everyone else who took a look!!