VLOOKUP Help

Melissa Dudley

Hello,

I want to look up the number of contact tracings interviews a staff member can do for "today's date." The contact tracers enter the number of interviews they think they can complete into a sheet called Contact Tracer Projected Capacity.

The Contact Tracer Projected Capacity sheet includes the following columns: Contact Tracer (contact list; column 1); Date (date; column 2); Day of Week (text/number; column 3); and Projected Capacity (text/number; column 4).

I'm running the calculations in another sheet called Capacity vs. Assigned. I was able to get the equation for VLOOKUP to work (see below); however, I don't know how to make it so it pulls the Projected Capacity number from the Contact Tracer Projected Capacity sheet for "today's date" only.

Any help would be GREATLY appreciated. Thank you!

=VLOOKUP([Contact Tracer]@row, {Contact Tracer Projected Capacity Range 1}, 4, false)

Mark Cronk

Perfect. Please accept my answer. Have a good week.

Mark

Mark Cronk

Hi @Melissa Dudley ,

VLOOKUP searches the left most column and returns results from the first row it finds a match. It's not looking for a date match.

If each person only has 1 capacity entry per day try using a SUMIFS. The response will be the number because there is only 1 entry. If there are multiple entries per day it will sum them.

The syntax is =SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, ​criterion2​... ]). You'll use the Capacity Column as the range because it contains the number you want returned. You'll use Contract Tracer column as a criterion range and = [contract tracer]@row. You'll use the Date column as your 2nd range and =today() as the criterion2. Make sense? Work?

Mark

Melissa Dudley

It worked!!!!!!!!!!!!! THANK YOU SOOOO MUCH!!!!

Mark Cronk

Perfect. Please accept my answer. Have a good week.

Mark