Solved

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

Posted on 2016-09-23
2
33 Views
Last Modified: 2016-09-23
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
0
Comment
Question by:Andreamary
2 Comments
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41813000
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 Closing Comment

by:Andreamary
ID: 41813083
Thanks, Ejgil, for the quick response...works like a charm! :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now