Excel - Formula to cross-check two inventories

Question

I am trying to build a formula to check between two inventory lists to ensure a given serial # is on both lists. I suspect a nested VLOOKUP is necessary but I cannot figure out how to construct the formula. Here is the setup:

Sheet1= Warehouse_1...contains a list of serial numbers (all in Warehouse 1)

Sheet2 = Master_Inventory...contains two columns: (A) Serial #s, (B) Location (dropdown: Ware1, Ware2, etc.)

I am seeking to add a third column in Sheet2 with a formula that says:

1. If Location=Ware1, check the corresponding Serial # in Column A against the Warehouse_1(Sheet1) list.

   • If found in Sheet1 list, return 'check mark'
   • If not found in Sheet1 list, return 'x mark'

2. If Location is NOT Ware1, leave blank

I would also like to maintain the ability to sort Sheet2 by Serial # and Location, meaning I don't want the formula to move with the entries as they are sorted.

Any help or ideas are greatly appreciated!

Answer 1

This picture illustrates a possibility:

The formula used in cell C1 is a composite of two steps, just like your description of the formula. First, the comparison to Ware1 is made via the IF formula (note: this formulat is only for illustration):

=IF($B1="Ware1", "CheckOrX...", "")

Then the check or x is selected by checking whether the serial number occurs on the Warehouse_1 sheet:

=IF($B1="Ware1", COUNTIF(Warehouse_1!$A:$A, $A1), "")

After entering that, select the bottom right corner of that cell and pull it down to have the formula copied to all the cells below.

Without formatting, this would lead to 0, 1 or blank values in column C. By using custom formatting, the 0s and 1s can be translated into the desired symbols by setting the following custom format:

√;;x

This can be achieved by select column C, do Format -> Cells..., select Custom and type this expression into the text box. For some explanation on the formatting see Create a custom number format