Formula to lookup up text in a table and list in sequential rows

See attached example. Using the data on the Data sheet, I want to choose the district from the drop-down list and have the results listed sequentially on the Output sheet. So, if you choose District 10, row 2 will be the first set of district 10 data, row 3 will be the next set of district 10 data.

Hi,

There is no formula that could do this for you in Excel, but it could certainly be done using some VBA. Try inserting this subroutine into the "Data" Worksheet module:

Please Login or Register  to view this content.

I have also attached the workbook for you to look at if you would like.

Hope this helps

Hahaha I've got to stop assuming things can't be done without VBA!

Works perfectly, thank you. You are a master!

I ran into a problem. What if in my data set for the district number there is text, it appears all results disappear? In that column my data could say "No Info", so how do I account for the text in the column?

Hi Brett,

I'm not sure who's method you implemented, but I don't think the VBA method that I provided you should stop working even if there is text in the District column.

Can you please upload what you have done in an attachment, and also state which solution you have attempted to implement so that the appropriate contributor can assist you with correcting it.

Thanks

I was using the formula version. I figured out a work-around for the text issue, but for some reason, when I use the formula as written above (but adapted for my actual worksheet), it always skips the first row of data it finds based on the district criteria. So, it is listing the results sequentially, but it always starts with the second set of data it finds, never the first. My actual formula is:

=IFERROR(INDEX(Table3,SMALL(IF((Table3[Column1]*1)='RSVP Analysis'!$J$2,ROW(Table3[Column1])-ROW('SOW Analysis'!$AC$2)+1),ROW($A2)),MATCH(A$2,Table3[#Headers],0)),"")

Hmmm, interesting behaviour.

I can have a look at the actual workbook and help you try to figure out the problem, or perhaps Sixthsense will see this post and be able to help you without the workbook.

Hahahaha just as I suspected. Thanks lol

Sixthsense,

I removed the *1 from the formula and I am still not getting the first set of data. It continues to start at the second set of data, so any other ideas to help make this work?

Also, if I want to display the results based on 2 sets of criteria, would my formula look like this if status of "Audit" was the second criteria?

=IFERROR(INDEX(Table3,SMALL(IF(AND((Table3[Column1]*1)='RSVP Analysis'!$J$2,Table3[Current SOW Status]="audit"),ROW(Table3[Column1])-ROW('SOW Analysis'!$AC$2)+1),ROW($A2)),MATCH(A$2,Table3[#Headers],0)),"")

Sixthsense,

Does your formula require the first set of data to be in Row 2 of the sheet? I had my sheet formatted with a title in Row 1, column titles in row 2, and your formula in row 3 (which was starting with the 2nd set of data), but when I deleted row 1 (with the data starting on row 2), now all of it appears. How do you indicate in your formula on which row to place the first set of data? Let's say I wanted it to start in Row 3 and go sequentially from there, am I changing +1 to +2?

Thanks. Since the output sheet displays the data sequentially, I cannot then apply a filter to sort the information. Is there a way to either sort the info once the output is finished or is there a way to sort the info as it is being looked up? For example, if the "Salesperson" was in column A and I had multiple Salespeople, can it sequntially list the data across the columns in a specific order...Salesperson A data, then Salesperson B data, etc...?

Sixthsense,

I had my report working and then something happened that I can no longer display any data in my report (I do not know what happened). It appears there is an error in my formula, but I cannot figure it out. Can you help?

Thanks

The only remaining issue is that using the recently attached example, if I change the default value in cell B18 to something other than "All Statuses", I get an error. Is the formula in cell D2 wrong with respect to that choice?

All,

Thank you very much. I believe my issue is resolved and my sheet is now working correctly. The final formula was:

{=IFERROR(IF(AND($B$18="ALL Statuses",$B$12="ALL Sales"),(INDEX(Table3,SMALL(IF(IFERROR(Table3[Column2]=$C$6,FALSE),ROW(Table3[Current SOW ID])-ROW('SOW Analysis'!$A$2)+1),ROW($E1)),MATCH(D$1,Table3[#Headers],0))),IF(AND($B$18="ALL Statuses",$B$12<>"ALL Sales"),(INDEX(Table3,SMALL(IF(IFERROR(Table3[Consultant]=$B$12,FALSE),ROW(Table3[Current SOW ID])-ROW('SOW Analysis'!$A$2)+1),ROW($E1)),MATCH(D$1,Table3[#Headers],0))),(IF($B$12="ALL Sales",(INDEX(Table3,SMALL(IF(IFERROR((Table3[Column2]=$C$6)*(Table3[Current SOW Status]=$B$18),FALSE),ROW(Table3[Current SOW ID])-ROW('SOW Analysis'!$A$2)+1),ROW($E1)),MATCH(D$1,Table3[#Headers],0))),(INDEX(Table3,SMALL(IF(IFERROR((Table3[Consultant]=$B$12)*(Table3[Current SOW Status]=$B$18),FALSE),ROW(Table3[Current SOW ID])-ROW('SOW Analysis'!$A$2)+1),ROW($E1)),MATCH(D$1,Table3[#Headers],0))))))),"")}