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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlanY

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


Book1
ABCDEF
1ArticleArticle's componentsStatus
2Box 1item 1passBox 1fail
3Box 1item 2failBox 2fail
4Box 1item 3pendingBox 3pending
5Box 1item 4fail
6Box 2item 1pass
7Box 2item 2fail
8Box 3item 1pass
9Box 3item 2pass
10Box 3item 3pending
Sheet3
Cell Formulas
RangeFormula
F2=IF(COUNTIFS($A$2:$A$10,$E2,$C$2:$C$10,"fail")>0,"fail",IF(COUNTIFS($A$2:$A$10,$E$2,$C$2:$C$10,"pending")>0,"pending","pass"))
 

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,342
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,941
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top