# Revising a LOOKUP formula finding last match to ignore rows if column K contains either of two values ("No Change" or "CX'd")

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", in which case it ignores this row.

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

I need to add an additional criteria to this formula so that it ignores all rows where column K in EE_Cat spreadsheet = "No Change" or "CX'd"

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.xlsx
###### Who is Participating?

Commented:
Try this
=IFERROR(LOOKUP(2,1/([EE_Cat.xlsx]Sheet1!\$I\$2:\$I\$1200=\$M3)/([EE_Cat.xlsx]Sheet1!\$K\$2:\$K\$1200<>"CX'd")/([EE_Cat.xlsx]Sheet1!\$K\$2:\$K\$1200<>"No Change"),[EE_Cat.xlsx]Sheet1!\$J\$2:\$J\$1200),"")
0

Author Commented:
Thanks, Ejgil, for the quick response...works like a charm! :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.