Solved

Excel Formula - VLOOKUP with Count

Posted on 2013-12-06
2
1,161 Views
Last Modified: 2013-12-06
I have the following formula in a cell, which will display either 'Y' or 'N' (possibly '')

=IF(OR($H288 = "", $H288 = "Monthly Totals:"), "", IF(ISERROR(VLOOKUP($H288,DecemberPayees,1,FALSE)), "N","Y"))

I use it as part of my 'check ledger' to make sure I've accounted for a recurring payee. If I've created an entry in the range 'DecemberPayees' (which changes monthly), a 'Y' will be displayed. In some cases, I make bi-weekly payments, which means that I really don't want to display 'Y' unless there are at least 2 (yes, I know there can sometimes be 3 pay periods in a month, if you figure out how to determine that and adjust the formula accordingly, I'll figure out some way to give you extra points).

Basically, for biweekly payments, the VLOOKUP needs to find 2 occurrences of the payment in the named range.
0
Comment
Question by:dbbishop
2 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 39701737
Hi dbb,

Since you're not using the return value of vlookup, why not use COUNTIF instead?

=IF(OR($H288 = "", $H288 = "Monthly Totals:"), "", IF(COUNTIF(DecemberPayees,$H288)=1, "N","Y"))

Change the =2 to test for 2, etc. If you want to see if its there at least one, then do >0 instead of =1.

Matt
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 39701789
Is there an A+ ? Thanks.
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 …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

23 Experts available now in Live!

Get 1:1 Help Now