+ Reply to Thread
Results 1 to 2 of 2

Compare Ranges/Return Specific Value for Matching Cells

  1. #1
    Registered User
    Join Date
    03-14-2006
    Posts
    12

    Compare Ranges/Return Specific Value for Matching Cells

    I'm stumped on this two-part issue and sure would appreciate any help.

    First, I have a range of cells (a1 through a5) which each contain an individual number.

    a1=196 and b1 presently blank
    a2=4 and b2 presently blank
    a3=67 and b3 presently blank
    a4=669 and b4 presently blank
    a5=345 and b5 presently blank
    I want to compare it with a second range (a16 through a20) and see if any of the cells have the same number.

    a16=7 and b16=red
    a17=222 and b17=blue
    a18=669 and b18=yellow
    a19=67 and b19=white
    a20=781 and b20=red
    Secondly, if any numbers between the two ranges match (i.e. A3 and A19 are both "67"), I want to copy the B column value for the number in the second range into the B column value for the matching number in the first range. Here's what the end result would look like:

    a1=196 and b1 stays blank
    a2=4 and b2 stays blank
    a3=67 and b3 becomes "white"
    (because white is the B column value
    of the matching number in the second range)
    a4=669 and b4 becomes "yellow"
    a5=345 and b5 stays blank
    Any ideas on how to accomplish this? Thanks so much!
    Last edited by VBA Noob; 01-06-2008 at 04:34 PM.

  2. #2
    Registered User
    Join Date
    03-14-2006
    Posts
    12

    Solution

    I finally found this solution by using the Help portion of the Excel program.

    Step 1: Highlight and name the range in which you want to look for comparison data. In this case, the range is a16:b20. Give it a name in the name box to the left of the equal sign in the formula menu row at the top of the sheet. For this example, name it "Test".

    Step 2: Go to B1 and enter
    =VLookup(a1,Test,2,false).

    A1 refers to the first cell for which you want to find a match within the second range (i.e the range we've named "Test"),

    "Test" refers to the name of the range we're searching for the match in.

    "2" refers to the column number to pull the value from in the event a match is found.

    "False" indicates that when comparing A1 and subsequent cells with the "Test" range, that we want to find only an exact match. ("True" would return similar or close results, but not necessarily exact matches).

    Step 3: Drag the formula down the column through B5.

    You're done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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