CODEWITHSUNDEEP
excelexcel-formulavba
eknofsky
eknofsky

Reputation: 163

Excel VLOOKUP with multiple possible options in table array

I have two lists, the first is a set of users. The second list contains different encounter dates for these users.

I need to identify the date that is within 10 days of the "Renew Date" [Column C], but not before. With Member 1 this would be row 3 1/8/2017. With Member 2 this would be row 6, 1/21/2017.

Now using a VLOOKUP which the user before me who managed this spreadsheet obviously isn't viable as it's simply going to pickup the first date that has a matching Member ID. Is there a way to do this in Excel at all?

I have included a link to a sample file and a screenshoit of the sample data.

https://drive.google.com/file/d/0B5kjsJZFrUgFcUFTNFBzQzN4cm8/view?usp=sharing

Example Excel Spreadsheet

Upvotes: 2

Views: 277

Answers (2)

A.S.H
A.S.H

Reputation: 29352

To avoid the slowness and complexities of array formulas, you can try with SUMIFS but the problem is that if you have more than one match, it will add them, not return the first match. The sum will look like an aberration. Will work however if you are guaranateed that you have only one match in the data.

An alternative is also to use AVERAGEIFS, which, in case of multiple matches, will give you their average and it will look like a valid date and a good result. Enter this formula in D2 and fill down the column:

D2:
=AVERAGEIFS(G:G,F:F,A2,G:G,">="&C2,G:G,"<="&C2+10)

and don't forget to format column D as Date.

Upvotes: 1

teylyn
teylyn

Reputation: 35990

Try this

=SUMPRODUCT($G$2:$G$7,--($F$2:$F$7=A2),--($G$2:$G$7<=C2+10),--($G$2:$G$7>C2))

Format the result as date. See screenshot (my system uses DMY order)

enter image description here

Don't use whole column references with this formula. It will slow down the workbook.

Upvotes: 1

Related Questions