asked on

Revise current Excel lookup formula to change source of lookup based on whether a specific column contains a date or not

I am currently using the following LOOKUP formula in spreadsheet EE_Pub (columns N, O and P) to find 'last match' values in spreadsheet EE_Cat (columns J, K and L), unless the value in column K of EE_Cat spreadsheet = "No Change" or "CX'd" in which case it ignores these rows.

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

I need to update the lookup formula to incorporate additional conditions:

If Column L of EE_Cat spreadsheet (Stage_4) column is blank, use the current lookup formula that finds last match:

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

If Column L of EE_Cat spreadsheet (Stage_4) column contains a date, then lookup formula needs to change from last match, to row that has the latest date in EE_Cat spreadsheet (Stage_4) column (based on Column M value in EE_Pub-Test spreadsheet as per the current lookup formula).

Note that Column L is always formatted as: AA–08 Jan 16 13:02

I have provided the two sample spreadsheets. I have highlighted the cells and included comments on the EE_Pub spreadsheet containing the results I am looking for with the updated formula.

I hope I have provided sufficient information, and that it is clear enough...

Thanks!
Andrea
EE_Cat.xlsx
EE_Pub-Test.xlsx

Ejgil Hedegaard

Try this
Formulas in columns R, S and T.
It is array formulas.
EE_Pub-Test.xlsx

Andreamary

ASKER

Hi Ejgil,

Thanks very much for providing this solution. All the results in columns R, S and T looked good except, for some reason, in Row 8. I've added comments to the specific cells in Row 8 showing the desired result (as attached).

BTW, can array formulas work in linked workbooks that are also shared? I know that shared spreadsheets are not recommended by this forum for good reason, and I apologize for this, but unfortunately our corporate environment use them (and I am not high enough up the food chain to change that policy). That being said, if the result in Row 8 is resolved using the array formulas you have provided, then I feel I should proceed to accept it as the solution for this question even if I can't use it, since I forgot to mention the shared, linked workbooks in my original question. If you can let me know if there is another solution that doesn't use array formulas, then I can post that as a separate question.

I look forward to your feedback...

Thanks,
Andrea
EE_Pub-Test_Solution.xlsx

Ejgil Hedegaard

I don't know what the problem is with the values.
On open the workbook show this in row 8 "Unique_7 | CLR |CF–21 Dec 15 13:46"
Changing links to the "EE-Cat.xlsx" file, the values change to "Unique_89 |CLR | CF–06 Jan 16 13:02"
That is the correct result.
Do you link to another file?
The file is not opened, so the array formulas work as link to the closed workbook.

I have never used a shared workbook, so don't know how it will work.
You have to try.

The array formulas are because the dates are found in a text with the Datevalue formula, and that lead to error if the argument is not a datevalue.
And blank cells are not a datevalue, so with If and Iserror statements errors are set to 0, and that require array formulas.
If the values in column L in Cat file was real dates, the formula can be normal.
See attached where the dates are in column M in Cat file, and the normal formula in column U in Pub file.
The search is done on column M, but the results are from column L.

Save both files in the same folder before opening, then the links should work.
EE_Pub-Test_Solution-A.xlsx
EE_Cat-A.xlsx

Andreamary

ASKER

Thanks very much, Ejgil, for your very helpful, detailed explanation, plus providing an alternative solution if the array formula isn't an option with a shared, linked spreadsheet...much appreciated!

I will have my first opportunity tomorrow to go over your answer/Excel files in detail so I can respond...

Cheers,
Andrea

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.