Need a formula to return the value in the Address column if 3 other criteria are met...

Hi Stanimal, I have 2 sheets in my example.
Sheet 1 displays the address next to the existing line if it matches the criteria.
Sheet 2 uses 2 helper columns to make a list of all addresses that meet the criteria starting at row2

Both sheets will look at the first 1000 addresses (any more than that and you'll need to copy formulas down)
They don't look for duplicates yet. Will have a look at that after a long sleep if no-one has fixed it first

Hey Beamer,

Thanks for the help on this. I actually have one page that is the evaluation page with the formulas and another page with the data so they will not correlate.

I need to be able to evaluate the entire column or at least B1:B10,000 and then be able to paste the formula down Column A of Worksheet B for instance and have it return all the addresses that meet the criterion without repeating any of the applicable addresses.

Let me know if you have something for that.

Thanks,

stan

The version I did on Sheet2 would work with some small changes (as in the example I attach here now), but you would still need to insert a helper column at the start of your data page. If you can do that then this will work fine.
If you can't add a helper column then maybe an array might work (not sure though), but I haven't got around to learning how they work yet.
This still doesn't sort out duplicates, that's my next step, will the duplicate address also have the same ID?
And one other thing, do you ever want to change your search criteria ("AL","Available",>90)?

Oh btw, I made the evaluation sheet import all of the data for each match. You can just delete the columns you don't want.

Thank you beamer for the effort.

FDibbins,

I cannot use a helper column because I am turning this over to a different department and they are only able to add data to the data sheet but will not be managing the helper column.

I used your first formula which appeared to be working in the actual worksheet until I reordered the data and then all the returned addresses changed completely.

Do you have any thoughts?

Thanks for the help.

It would need to be copied down as the column increases. We buy 300-400 new properties/mo so we will need to keep adding to the list.

Also, when I used it on a 4,000 address worksheet, the formula returned completely different addresses - that didn't meet the "IF" Criteria.

Let me know if u have additional ideas. Thx

FDibbins,

Alright, I have attached a sample file with 1,000 records. This is actual data, but without some of the additional columns we are using.

Sheet 1 contains your formula with the data sorted so that the matches appear at the top. Sheet 2 was resorted and the evaluation column went blank. Same thing happened on Sheet 3 when I sorted by different criteria.

In the past it was doing something different but I was unable to duplicate it.

Additionally, I would like to return additional data from the same line in adjacent columns, but I think we need to get this squared first.

Please advise.

Thanks,

Stan

Beamer,

I appreciate the additional effort, I can tell it took some time. Unfortunately, I cannot have helper columns on the same sheet or on the data page. They will not allow it.

Let me know if you have any additional thoughts.

Thank you again for the help.

Chat soon