+ Reply to Thread
Results 1 to 4 of 4

Need a VLOOKUP formula explained

  1. #1
    Registered User
    Join Date
    02-01-2016
    Location
    centennial, CO
    MS-Off Ver
    2007
    Posts
    5

    Need a VLOOKUP formula explained

    Hello all. I'm using Excel 2007 and I'm a novice with with it. I use google when I need help for formulas and shortcuts which is what lead me to this forum. I have a data set that is 7 columns wide and 322,000 rows deep. I needed a formula to find if data in column E matched any data in column A and mark it so I could delete those rows that matched. I came across this formula and copied into my sheet -

    =IF(ISNA(VLOOKUP(E1,$A$1:$B$65535,1,FALSE)),"not in A","")

    It works and now I am trying to understand basically how the formula works. I do understand the ISNA funtion and the VLOOKUP function as well. The one part I am struggling with is what "$A$1:$B$65535" in the middle of the formula actually is doing. I only what to compare data from column E and column A. I know the $A$1 is the starting point, but what is :$B$65535? Is the code looking beyond the last data point in column A and going to column B:row 65535 for the comparison?

    Thanks - Gary

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,080

    Re: Need a VLOOKUP formula explained

    Given the range A1:B65535 VLOOKUP searches for the value in E1 but only in the first column (from the ",1," part), ie column A
    So the B65535 is redundant and could equally have been A65535 since only one column is being searched.
    FALSE means it must find the exact value.
    If the result is #N/A then the result is "Not in A" otherwise if found the result is null ("")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Need a VLOOKUP formula explained

    VLOOKUP's are typically used with multiple columns but that really isn't necessary. They could have used the formula

    =IF(ISNA(VLOOKUP(E1,$A$1:$A$65535,1,FALSE)),"not in A","")

    The addition of the B column does not add anything.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    02-01-2016
    Location
    centennial, CO
    MS-Off Ver
    2007
    Posts
    5

    Thumbs up Re: Need a VLOOKUP formula explained

    Excellent. Thanks for the explanation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Array formula Explained For Everyone (this is a reference sheet)
    By frankzolf in forum The Water Cooler
    Replies: 15
    Last Post: 05-20-2014, 01:02 PM
  2. Array formula Explained For Everyone (this is a reference sheet)
    By frankzolf in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2014, 09:56 PM
  3. Please help how to get the Formula for below explained...!
    By mzaheer_abbas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2014, 02:17 AM
  4. Not sure if its explained well, but hope it can be done....
    By Joe O Ceadaigh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-29-2012, 01:50 PM
  5. SUMIF formula explained
    By 92811 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2011, 05:07 PM
  6. need help with a v lookup formula, just need it to be explained
    By damagedbodies in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2010, 01:08 PM
  7. DLL's explained?
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2008, 08:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1