VLOOKUP or INDEX/MATCH?

**scratchdp** · 11-16-2011, 02:49 PM

Hello,

I have a look up table that I would like to populate with where a client was in the pipeline on a particular date. I would like to do a VLOOKUP on my activity table, but only look for entries prior to a given date. Does anyone have any suggestions? I have attached a sample file.

Thank you!

**NBVC** · 11-16-2011, 03:06 PM

Are you trying to count matches to the first 2 columns and then date in first row.

like:

=COUNTIFS(Activity!$A$2:$A$8,$A2,Activity!$B$2:$B$8,$B2,Activity!$C$2:$C$8,C$1)

copied down and across.

**scratchdp** · 11-16-2011, 03:26 PM

Sorry if I didn't describe properly what value I wanted to get back. I want to display the "Pipeline Stage" on or before a particular date.

Example: If I did a VLOOKUP for LC1000117 in the activity table, I would get "Lost" for the "Pipeline Stage". VLOOKUP(A2,Activity!A:B,2,FALSE)
What I want to know is what stage was it in on 11/14/2011. So I want to do a lookup but only of values where "ActivityDate" is <=11/14/2011.

In my sample set the answer for LC1000117 on 11/14/2011 should be "No Reply".

**NBVC** · 11-16-2011, 03:38 PM

Try:

=IFERROR(INDEX(Activity!$B$2:$B$8,MATCH(1,INDEX((Activity!$A$2:$A$8=$A2)*(Activity!$C$2:$C$8=C$1),0) ,0)),"")

copied down and across

**scratchdp** · 11-16-2011, 03:50 PM

Thanks for the suggestion, but this still does not provide the appropriate values on dates that don't have records. For example, on 11/12/2011 the values should be identical to those on 11/11/2011 (as there was no activity on 11/12).

**NBVC** · 11-16-2011, 03:59 PM

Okay then try:

=IFERROR(INDEX(Activity!$B$2:$B$8,MIN(IF((Activity!$A$2:$A$8=$A2)*(Activity!$C$2:$C$8<=C$1), ROW(Activity!$B$2:$B$8)-ROW(Activity!$B$2)+1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down and across