Comparing VLookup Results vs. Other Values?

Posted by G on September 06, 2000 2:06 PM

I've got a 3500 row spreadsheet (50 columns). Column A lists product id numbers, Column D lists "Instock" status (1 or 0), Column E lists "Discontinued" status (1 or 0).

I'll be receiving an update of this list every week from a vendor....what I want to do is highlight product id's where either the Instock or Discontinued status has changed.

I've tried to create an IF/VLOOKUP statement:
=IF(D2=(VLOOKUP(A2,'[Funworld New.xls]Sheet1'!$A$2:$A$4000,4,FALSE)),0,"Changed") so that I can reference the New sheet vs. the old sheet, but I keep getting #REF! errors.

Thoughts?
Better ideas?
TIA

Posted by G on September 06, 0100 3:18 PM

Thanks anyway!

Posted by Doug on September 08, 0100 8:36 PM

TIA,

I think you are on the money with the IF / VLOOKUP Approach

I would approach your problem like this:
Add three columns to your spread sheet
Column 1: Stock Change
=IF(VLOOKUP(D2,'Update List'!A:B,2,FALSE)=E2,0,1)
** RETURNS 1 if STATUS CHANGED **
Column 2: Discontinued Change
=IF(VLOOKUP(D2,'Update List'!A:C,3,FALSE)=F2,0,1)
** RETURNS 1 if STATUS CHANGED **
Column 3 is just an indicator column with an * if either value has changed
=IF(A2+B2>0,"*","")

Extra Points:
You can hide the first two columns showing only the * if needed
If you copy and paste your new data into the referenced spreadsheet ("Update List" in my example) you can avoid updating your formulas in your spreadsheet weekly.

Hope this helps

Doug