Excel 2007 : Need to match data and find missing

**topgun3406** · 04-02-2011, 05:31 PM

Hi, i am a user on a Paid to Click site and i try to maintain data on how my referrals click on a spread sheet. Im having problems using the ISNA and the VLOOK UP formulas. I import data from the site that im using everyday. Some times referrals which do not perform get replaced. So, in my sheet i want to find out the referrals which have been replaced.

I have been searching on google on how to solve this problem and i still haven't been able to understand these 2 functions.

I have 2 sheets namely "input" and "daily clicks". The input sheet is where i import data daily from the site. The daily clicks sheet is used to record the referrals performance.

I want the "missing" or "replaced referrals" to show up on the daily clicks sheet after i import the data in the "input" sheet.

The "referral since" column is sorted as per Oldest to Newest. And the "referral id" column is sorted "A to Z".

Currently, the data in the input sheet is of the date 3rd april. This is the data i want to add to my daily clicks sheet where in the missing referrals will show up.

I have attached a copy of my sheet so u can understand better.

I hope im not troubling you guys and i hope u can understand what i have tried to explain :D . Any help will be highly appreciated! thanks!

**Cutter** · 04-02-2011, 05:59 PM

You're not troubling anyone.

You should upload your file directly to this forum instead of providing a link to another site.
Click on the FAQ button at top of page and follow the links to get instructions for that.

In the meantime here are some links for you re ISNA() and VLOOKUP()

http://www.excelfunctions.net/Excel-Isna-Function.html

http://www.excelfunctions.net/ExcelVlookup.html

http://www.excelfunctions.net/Excel-...-Tutorial.html

The typical way to use the ISNA() VLOOKUP() formula is this:

=IF(ISNA(VLOOKUP(),"something",VLOOKUP())

The "something" is what you want to display if your VLOOKUP() produces #N/A - in other words when Excel can't find what you tell the VLOOKUP() to look for.

**topgun3406** · 04-02-2011, 06:42 PM

I have attached the worksheet after getting the info from the FAQ.

On my daily clicks sheet , i entered the following formula:

=IF(ISNA(VLOOKUP(B3, Input!#REF!,1,0)),"MISSING","Okay")

It shows "okay" for all referrals when it should list the last one as missing. I have attached the worksheet after entering the above formula. The last referral was replaced and hence it should show up as missing.

Thanks for the links. I understand better now how it works.. but still unable to apply it to my situation :D

**Cutter** · 04-02-2011, 07:07 PM

If you simply want to know if an entry on Daily Clicks is present on Input you can use this:

=IF(COUNTIF(Input!B:B,B3),"Okay","MISSING")

for the first row and copied down.

**topgun3406** · 04-02-2011, 07:29 PM

Originally Posted by Cutter

If you simply want to know if an entry on Daily Clicks is present on Input you can use this:

=IF(COUNTIF(Input!B:B,B3),"Okay","MISSING")

for the first row and copied down.

Thanks a lot. That works very well!

You made things really easier for me.. Thanks again

**Cutter** · 04-02-2011, 07:31 PM

You're welcome.

Don't forget to mark your thread as SOLVED.

Oh, congratulations on India's cricket win.

Excel 2007 : Need to match data and find missing

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need to match data and find missing

Re: Need to match data and find missing

Re: Need to match data and find missing

Re: Need to match data and find missing

Re: Need to match data and find missing

Re: Need to match data and find missing

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions