CODEWITHSUNDEEP
excelexcel-formulavlookupworksheet-functionsumifs
George
George

Reputation: 4674

VLOOKUP with IF conditions are met

I have this problem at work to populate the worksheet with the right case number. 

Sheet 1: (Report)
SSN         | Service Date
123456      | 10/01/2014

Sheet 2: (Data)
SSN         | Case Number  | Start Date | End Date
123456      | 0000000      | 01/01/2010 | 12/31/2012
123456      | 1111111      | 01/01/2013 | 05/31/2014
123456      | 2222222      | 06/01/2014 | 11/10/2015

How can I do a VLOOKUP based on the Service Date to be within the "range" of the Start and End Date of another sheet?

In this case I would like to lookup the SSN and return case number 2222222 because that is the case active for such date of service.

I was looking online and found "MATCH". I am able to match the first result of the case matches the SSN, but how to go to the next case if it does not match?

=IF(E2>=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),4)&E2<=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),5),"YES","NO")

I am using Excel 2013 on Windows 7 at work.

Upvotes: 0

Views: 1085

Answers (3)

Dirk Reichel
Dirk Reichel

Reputation: 7979

This array-formula will always print the last match:

=INDEX(Sheet2!B:B,MAX((Sheet2!A:A=A2)*(Sheet2!C:C<=B2)*(Sheet2!D:D>=B2)*ROW(A:A)))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

  • It works if there are multiple solutions which fit the criteria
  • It also works with every kind of data you want to show (values/dates/strings)

! However, you should cut the range as short as possible. (its a huge calculation for the entire sheet)

Upvotes: 1

user4039065
user4039065

Reputation:

You will need 3 conditions. a) Is the start date less than the Service Date b) Is the End Date greater than the Service Date and c) do the SSN numbers match?

Use the newer AGGREGATE¹ function to force any non-matches into an error state while using the ignore errors option (e.g. 6) to discard errors.

=INDEX(Sheet2!$B$2:$B$9999, AGGREGATE(15, 6, ROW($1:$9998)/((Sheet2!C$2:C$9999<=B2)*(Sheet2!D$2:D$9999>=B2)*(Sheet2!A$2:A$9999=A2)), 1))

      Multiple criteria VLOOKUP

For all intents and purposes, a worksheet formula treats FALSE as zero (e.g. 0) and TRUE as one (e.g. 1). Any number multiplied by zero is zero and any number multiplied by one is the same number. The AGGREGATE function is retrieving the row position of the first match within Sheet2!B2"B9999. That row position will be a number somewhere within ROW(1:9998). Any of the rows that do not match all three condition will have at least one zero multiplied by the denominator. This makes the denominator zero. Anything divided by zero forces a #DIV/0! error and AGGREGATE will discard those from the result set. AGGREGATE's 15 option is the SMALL and the last 1 is the k ordinal for SMALL (the very smallest). So of all the rows that match all three conditions, AGGREGATE returns the lowest one to the INDEX function which retrieves the value from Sheet2!B2"B9999.

Tighten the ranges up to a maximum of 5 rows and use the Evaluate Formula command to step through the formula and gain a better understanding.

It may be worthwhile to note that it is very easy to convert this formula to retrieve the second, third, etc. matches as well since it only requires sequencing the k ordinal up.

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 3

pnuts
pnuts

Reputation: 59475

If SSN is in A1 of both sheets and your Case Numbers are numeric (other than 0000000) then you might try: 

=SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"<="&B2,Sheet2!D:D,">="&B2)

SUMIFS is explained here (and elsewhere!).

Upvotes: 2

Related Questions