lookup a list and return multiple values

[gordon230786]

I have been doing a lot of reading up on this subject and I think im getting closer to understanding what im meant to do however when I put into practice with a more complicated search argument it falls to pieces.

I have a complicated workbook which a main tab with 60 odd columns and thousands of rows. everyone of these cells has information of some sort in it.

I want to create a list in a different tab which only returns information information based on 2 columns. ie.

only return the information in cells in column A which meet the argument 'column D matching Sheet2!A1 and in column BD everything under 3.

I don't know if hiding rows that arent relevant effects anything.

I have also been trying to use a formula in similar style to this,

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

but using it as an array formula by pressing CTRL+SHIFT+ENTER.

I know that using something similar to that and dragging down does work on simpler tasks so I was wondering if someone could either modify it to work for me or come up with a better alternative.

My Spreadsheet is located at google docs and I have made it available for public use. Here is the link

https://docs.google.com/file/d/0B4WzyLzE3TLkR2NPWENTVGJ6XzQ/edit

You can go to file and then download to work on it on your own computer.

A good example would be if you could try this please would be go to tab 3300, and start inputting the formula into cell A13 to match the arguments 'Cable Data!BD:BD,"<3" and 'Cable Data!D:D,Summary!A9 to return all cells in column A that match this argument.

If anyone could help I would appreciate this massively. Thank you.

 

[Domski]

Have you looked at using Advanced Filter?

Dom

 

[Aladin Akyurek]

It seems the file on Google Docs is not reachable.

However, try to adapt from post #3 in:

http://www.mrexcel.com/forum/showthread.php?307832

 

[gordon230786]

the file was reachable for me.

I have got the formula like this which seems close but still returning a few unwanted values.

=IF(AND(BD$2:BD$331<=2,D$2:D$331,Summary!A$9),A$2:A$331)

is this correct the correct way of writing it? where the green text and cyan text are correctly inputted for AND function? and if so they will return the red data?

 

[Aladin Akyurek]

the file was reachable for me.

Clicking on the link should lead to loading the file. The loading process fails and IE issues the "cannot display the page" message.

I have got the formula like this which seems close but still returning a few unwanted values.

=IF(AND(BD$2:BD$331<=2,D$2:D$331,Summary!A$9),A$2:A$331)

is this correct the correct way of writing it? where the green text and cyan text are correctly inputted for AND function? and if so they will return the red data?

It's not. Correction is only possible if the lay-out and the intent are known. It seems the link I provided doesn't help. Care to post here a tiny sample along with desired results?

 

[Robert Mika]

This is based on the 3230 tab but use this same formulas for all tabs:

First Cable Data:
=IFERROR(VLOOKUP(U2,'Cable Status'!$A$1:$B$8,2,FALSE),"")

to avoid #N/A error.
Then in each tab:
K2:
=--REPLACE(CELL("Filename",A4),1,FIND("]",CELL("filename",A4)),"")

to retrive tab name.
L2:
=COUNTIFS('Cable Data'!$D$2:$D$20000,K2,'Cable Data'!$BD$2:$BD$20000,"<3")

Finally A2 copy down and accross confirm Control+SHift+Enter:


=IF(ROWS($O$2:$O2)<=$L$2,INDEX('Cable Data'!$A$2:$CC$20000,SMALL(IF('Cable Data'!$D$2:$D$20000=$K$2,IF('Cable Data'!$BD$2:$BD$20000<3,ROW($B$2:$B$20000)-ROW($B$2)+1)),ROWS($B$2:$B2)),MATCH(A$1,'Cable Data'!$A$1:$AY$1,0)),"")

 

[gordon230786]

This is based on the 3230 tab but use this same formulas for all tabs:

First Cable Data:
=IFERROR(VLOOKUP(U2,'Cable Status'!$A$1:$B$8,2,FALSE),"")

to avoid #N/A error.
Then in each tab:
K2:
=--REPLACE(CELL("Filename",A4),1,FIND("]",CELL("filename",A4)),"")

to retrive tab name.
L2:
=COUNTIFS('Cable Data'!$D$2:$D$20000,K2,'Cable Data'!$BD$2:$BD$20000,"<3")

Finally A2 copy down and accross confirm Control+SHift+Enter:


=IF(ROWS($O$2:$O2)<=$L$2,INDEX('Cable Data'!$A$2:$CC$20000,SMALL(IF('Cable Data'!$D$2:$D$20000=$K$2,IF('Cable Data'!$BD$2:$BD$20000<3,ROW($B$2:$B$20000)-ROW($B$2)+1)),ROWS($B$2:$B2)),MATCH(A$1,'Cable Data'!$A$1:$AY$1,0)),"")

Thanks! I don't quite understand that but I guess it would work.

I have got this formula in each tab with the correct cell from the Summary tab, ie tab 3210 - Summary!A$2, 3220 - Summary!A$3, and so on. however I am getting FALSE results for every line in the 'Cable Data' it doesnt match, but it does return the cell in column A when the search argument is met.

How could I get this formula to not include FALSE and only return the values which are 'true' (column A info).

Here is the formula,

=IF(AND('Cable Data'!BD:BD<=2,'Cable Data'!D:D=Summary!A$4),'Cable Data'!A:A)

Thanks