Search list for new addition

[Mac Landers]

Good Morning!

I have a list of products that comes from another group. Occassionaly a new
customer is added and they do not inform us - when we run our formulas to
populate the numbers it doesn't populate; however, it is difficult to notice
because there are not always numbers for the products so there are many blank
cells and it just appears that there were no orders. I am looking for a way
to sweep through the list of products and find any products that do not match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

 

[Bernie Deitrick]

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False)),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

 

[Mac Landers]

Great, thanks!!

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False)),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

 

[Mac Landers]

I have one problem as I continue to build this. I have the formula reading
each row (cell with new value) and then it looks it up in my table (old
table). However, the formula copies down exactly for every row - thus all
the rows equal what the first row returns. I do not have it locked with $
and when I try to manually change it, it updates all the cells to equal the
formula I changed. How do I get each row to read the cell value for its row?
Ex. If(ISERROR(Match(D2,$A2:$A29,FALSE)),"This is not in our table!","Ok")
this formula copies all the way down, I want it to be D3, D4, D5 extra.

Thank you!

 

[Bernie Deitrick]

Mac,

Use this:

=If(ISERROR(Match(D2,$A$2:$A$29,FALSE)),"This is not in our table!","Ok")

Use copy and paste of the cell, not of the cell contents (don't select the
formula from the editing bar). Simply select the cell prior to using Copy,
and the formula should update correctly.

HTH,
Bernie
MS Excel MVP

 

[Mac Landers]

I am up and running. Thank you so much, especially for your quick responses.

 

[Bernie Deitrick]

Mac,

Glad to hear it, but wait 'til you see my bill ;-)

Bernie
MS Excel MVP