Vlookup function with if statement possibility?

Lupirion

New Member
Joined
Feb 18, 2019
Messages
3
Hi every one. Im new here.

I have a question if its possible. i work with excel every day but i'm basically a noob :(
Here is the situation i have. i have to go through a list of a lot of articles to take out the information from it ( if it is pass fail or pending)
i dont wanna keep going through every single box to pick fail, pending or pass.

Is there a way to use Vlookup or any other way to tell excel:
"ok look at box 1 if you see fail in any of the items then show me just fail if not then pending if not then pass etc"

ArticleArticle's componentsStatus
Box 1item 1pass
Box 1item 2fail
Box 1item 3pending
Box 1item 4fail
Box 2item 1pass
Box 2item 2fail
Box 3item 1pass
Box 3item 2pass
Box 3item 3pending

<tbody>
</tbody>

i might be busy and i might forget to reply to you guys :( sorry if that happens.

Thank you for your help
Have a nice day
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Article</td><td style=";">Article's components</td><td style=";">Status</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Box 1</td><td style=";">item 1</td><td style=";">pass</td><td style="text-align: right;;"></td><td style=";">Box 1</td><td style="background-color: #E2EFDA;;">fail</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Box 1</td><td style=";">item 2</td><td style=";">fail</td><td style="text-align: right;;"></td><td style=";">Box 2</td><td style="background-color: #E2EFDA;;">fail</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Box 1</td><td style=";">item 3</td><td style=";">pending</td><td style="text-align: right;;"></td><td style=";">Box 3</td><td style="background-color: #E2EFDA;;">pending</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Box 1</td><td style=";">item 4</td><td style=";">fail</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Box 2</td><td style=";">item 1</td><td style=";">pass</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Box 2</td><td style=";">item 2</td><td style=";">fail</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Box 3</td><td style=";">item 1</td><td style=";">pass</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Box 3</td><td style=";">item 2</td><td style=";">pass</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Box 3</td><td style=";">item 3</td><td style=";">pending</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$10,$E2,$C$2:$C$10,"fail"</font>)>0,"fail",IF(<font color="Red">COUNTIFS(<font color="Green">$A$2:$A$10,$E$2,$C$2:$C$10,"pending"</font>)>0,"pending","pass"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Lupirion

New Member
Joined
Feb 18, 2019
Messages
3
OMG that is perfect :D exactly what i needed

Thank you Alan

I presume i can add more countifs? say if i have not applicable or not relevant etc. the same way?

thanks again :)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome.

ans yes you can add more criteria i countifs() as needed
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,906
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top