How can I use Vlookup to check multiple task fulfilment?

Hello,

I'm a new boy. I assume that there will be some sort of initiation/hazing so, bring it on!

Right here's the problem, for which I would be grateful for a solution suitable for a remedial class member such as myself:

I have a number of separate manufacturing tasks which require several parts to complete. I need to establish whether there are sufficient parts to complete all of the sub-tasks and therefore the whole task. The first bit is easy, but what I am struggling with is counting the number of sub-tasks and comparing that to the number of the sub-tasks which can be completed e.g. there are five sub-tasks to complete the task, if all the sub-tasks are fulfilled, then return a value "yes" if not, return a value "no."

Specimen Spreadsheet publisher.gif

I look forward to getting to being humbled.

can you upload the workbook itself? (follow the same process used to upload that gif file, but select a shareable version of the excel file).

One way to do this would be to count the number of "yes" entries and compare the to total number of entries with 2 sumproduct functions.

Example:

=CHOOSE((SUMPRODUCT(($A$5:$A$20=A5)*($A$5:$A$20=A5))=SUMPRODUCT(($A$5:$A$20=A5)*($E$5:$E$20="yes")))*1+1,"no","yes")

Try that adjusted for your ranges

Yep. That works (note, you had the wrong label for the Task2 B column (you didn't have Task2/...)


Anyway, is the attached file what you are after? I changed some numbers in the task 2 area to test if the formula would evaluate properly

@Baloo, you will suffer minimal hazing if you review the Forum Rules you already agreed to follow when you registered. Hehe. Link is above in the menu bar.

@ General - I've never heard of the CHOOSE() function before. It looks interesting and I'll have to play around with it.

Another way would be to use =IF(SUMPRODUCT(--($A:$A=A5)*($E:$E="Yes"))=SUMPRODUCT(--($A:$A=A5)),"Yes","No"). I'm just providing options.

Yeah, the CHOOSE function is handy to know. Usually, you can use it anytime you feel the need for an IF() statement. It's usually just easier to read through.

Granted, in a case like this the IF statement is just fine - but when you start nesting the IF statements using CHOOSE really starts to simplify things. Like anything in excel, more than one way to skin a cat but after you get used to CHOOSE in tough cases you'll find yourself using it for simple ones also.