VLOOKUP Help
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)
Best Answer
-
Perfect. Please accept my answer. Have a good week.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
It worked!!!!!!!!!!!!! THANK YOU SOOOO MUCH!!!!
-
Perfect. Please accept my answer. Have a good week.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives