Need formula to provide search criteria from table

[zenix]

Hi to all the and thanks for the great work you are doing here. I would like a formula that will provide the search criteria on the right from the data on the left. Your assistance is very appreciated.

[congnt92]

Hi Zenix
Why do you need a formula while simple sort + filter can help you.

[FlameRetired]

You need to clean up some data. The reference street is misspelled as is the street name in B11.

If you still want a formula solution I would recommend two helper columns. One in column G

Please Login or Register  to view this content.

and another in column H of the attached. (I moved the output range over a column.)

Please Login or Register  to view this content.

Then if you do not want Apt numbers to return 0s for the blanks array enter this in cell I5, fill down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Without array formula you get 0s but you do not have to array enter.

Please Login or Register  to view this content.

[congnt92]

Or try this

Please Login or Register  to view this content.

Copied down and copied right.
But still I suggest you using sort + filter because your data is very large. Formula will take a little a bit of time to calc.