Indexing Question

Marlana K.

Need help with a formula that will hopefully eliminate the need for an additional "helper column".

We have built an API that pulls various information regarding our 700+ Pharmacies from our data base into a Pharmacy Site Information (API) Sheet. We use INDEX/MATCH frequently to pull in various fields. My current problem is this:

We have a column Open Date in the API Sheet that will list the date the Pharmacy opened. When we have new pharmacies that are in an implementing status the date will show as 01/01/00 (January 1, 1900). What I would like to do is leave the cell blank if the date in the Open Date Column is 01/01/00. I have tried different combinations of IF and INDEX/MATCH with no success.

The only way I can get what I need is to use a helper column which I really hate having so many helper columns.

Current process/formulas

Column Pharmacy Opening Date (Indexing) as up see the date is pulled in those sites that are not yet open 20446 and below reflect the 01/01/00 date.

=IFERROR(INDEX({Pharmacy Site Information (API) Open Date}, MATCH([Site Number (helper)]@row, {Pharmacy Site Information (API) Site #}, 0), 1), "")

Column Pharmacy Opening Date

This is an IF statement that looks at the Pharmacy Opening Date (Indexing) Column and if the date is equal to 01/01/00 then it leaves the cell blank otherwise it will insert the actual date.

=IF([Pharmacy Opening Date (Indexing)]@row = "01/01/00", " ", [Pharmacy Opening Date (Indexing)]@row)

HOW can I get the INEDX/MATCH to look at the date and if it is 01/01/00 leave it blank? All in one formula?

Thanks!

Marlana Kalinowski

Paul Newcome

All you need to do is take the formula that is in the helper column and drop it in place of the helper column's cell reference in the IF statement.

=IF(IFERROR(INDEX(....., MATCH(.....)), "") <> DATE(1900, 01, 01), IFERROR(INDEX(....., MATCH(.....)), ""))