VLOOKUP/IF returning two different results

[USAFpyro]

Hello,

I am working on a formula to keep inventory of uniforms and I need some help with the formula.

I have two sheets in the workbook. In sheet one, column A is a master list of uniform numbers (1-240). Column B is a "status" column that will display the result of the formula (resulting in either "IN","OUT",or "MISSING").

The second sheet consists of a list of names in column A. Column B is where the number of the uniform will go for each person. Column C is where missing uniform numbers will go.

What I am trying to do, is get the "status" column of sheet one to display "IN" if the number is not found in either columns B or C of the second sheet, "OUT" if it finds the number in column B of the second sheet, or "MISSING" if the number is found in column C of sheet two.

Any assistance would be greatly appreciated.
Thank you,
Devon

 

[USAFpyro]

I am currently using this formula which works for getting the "IN" and "OUT" results but am unsure how to get it to display the third result.

=IF(ISNA(VLOOKUP(A3,Sheet2!B:B,1,FALSE)),"IN","OUT")

 

[Akashwani]

Hi Devon,

Welcome to MrExcel.

I may be heading in the wrong direction with this, but does this work?...

Put this formula in B2 of sheet1 and copy down to B241

=IF(COUNTIF(Sheet2!B:B,A2),"out",IF(COUNTIF(Sheet2!C:C,A2),"Missing","IN"))

Ak

 

[Aladin Akyurek]

I am currently using this formula which works for getting the "IN" and "OUT" results but am unsure how to get it to display the third result.

=IF(ISNA(VLOOKUP(A3,Sheet2!B:B,1,FALSE)),"IN","OUT")

This will look possibly as unusual...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,MATCH(A3,Sheet2!B:B,0),
    MATCH(A3,Sheet2!C:C,0)),{"IN","OUT","MISSING"})

 

[USAFpyro]

Hi Devon,

Welcome to MrExcel.

I may be heading in the wrong direction with this, but does this work?...

Put this formula in B2 of sheet1 and copy down to B241

=IF(COUNTIF(Sheet2!B:B,A2),"out",IF(COUNTIF(Sheet2!C:C,A2),"Missing","IN"))

Ak

THAT WORKED PERFECTLY!!! Thank you sooooo much!! I cannot express how grateful I am. I never thought to use a COUNTIF function with this.

Devon

 

[Akashwani]

Hi Devon,

If I were you, I'd try the formula provided by Aladin, he's the expert.

Good luck with your project.

Ak