Vlookup - Puzzling

[Frank Bacchus]

I have the following vlookup statement:

=IFERROR(VLOOKUP(C6,'Critical Links -Weekly_changes'!$A$2:$K$158,5,FALSE),"")

One of the cells (K) in 'Critical Links -Weekly_changes' (above) has "Yes" or "No". I only want to Execute the above Vlookup if the contain of cell "K" has "Yes". How can I approach this. Thanks in advance.

frank

 

[PCosta87]

Hi,

I'm not sure I understood... can you upload a sample file?
Thanks

Scratch that... use INDEX/MATCH with 2 criteria (C6&"Yes").
It should look like:

=IFERROR(INDEX($E$2:$E$158,MATCH(C6&"YES",$A$2:$A$158&$K$2:$K$158,0)),"")

Complete with Ctrl+Shift+Enter

 

[Asheesh]

Share the sample file..I am of suspicion that it can be done without CTRL + SHIFT + ENTER (Array)

 

[Khalid NGO]

Hi Frank,

If your 5th column contains numbers only, you can try this with just enter:

=SUMIFS('Critical Links -Weekly_changes'!$E$2:$E$6,'Critical Links -Weekly_changes'!$A$2:$A$6,C6,'Critical Links -Weekly_changes'!$K$2:$K$6,"yes")

If not, try this with CSE:

=IFERROR(VLOOKUP(C6,IF('Critical Links -Weekly_changes'!$K$2:$K$158="Yes",'Critical Links -Weekly_changes'!$A$2:$K$158),5,FALSE),"")

Regards,