• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

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...

1 Solution
Ejgil HedegaardCommented:
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),"")
AndreamaryAuthor Commented:
Thanks, Ejgil, for the quick response...works like a charm! :-)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now