Reverse of what my vlookup does

[4lowie]

Hi all
I have a vlookup which checks to see if a value is present in 'Order Tracking Sheet' and it works.... How do I rewrite this to show when a value in the tracking sheet doesn't match where my data is? Eg removed. What happens now is the we have a sheet that "displays data", we also have a "dump sheet" for our fresh data downloads. My vlookup shows if the data in the "dump sheet" isn't on the "display sheet". What i need is to show on the "display sheet" if the data is no longer in the "data bump" sheet.

Appreciate your help.

Dave

=VLOOKUP(B37,'ORDER TRACKING SHEET'!A:A,1,FALSE)

[AliGW]

If I have understood correctly, your formula will return #N/A if the value in B37 is not on the order tracking sheet. Or have I not got what you're getting at? Probably not ...

[TMS]

Please Login or Register  to view this content.

Is that what you're looking for?


Regards, TMS

[oeldere]

Advice

'ORDER TRACKING SHEET'!A:A

Don't use full columns for the VLookup, it will slow down the performance of the file.

use a range which is long enough e.g.

'ORDER TRACKING SHEET'!A1:A15000

[4lowie]

Thanks guys

Thinking about this some more and what I'd really like to see a line in the "ORDER TRACKING SHEET" change color if its no longer in the "SAP DUMP" sheet. I tried this with conditional formatting but couldn't get to work.

How hard is it to do it this way?

Thanks Dave

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tony Valko]

Don't use full columns for the VLookup, it will slow down the performance of the file.

On my machine, in Excel 2002, I filled columns A and B with random data.

I tested these two formulas:

Formula 1: =VLOOKUP(D1,A1:B900,2,0)

Formula 2: =VLOOKUP(D1,A:B,2,0)

Here are the calculation times for the formulas:

Data Range

	E	F
1	Formula 1	Formula 2
2	0.00017	0.00017
3	0.00017	0.00017
4	0.00018	0.00018
5	0.00018	0.00017
6	0.00017	0.00017
7	0.00017	0.00017

The lookup value was on row 850.

As you can see the calculation times are almost identical whether referencing entire columns or a smaller specific range.

I used Charles Williams calculation range_timer method found here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx