+ Reply to Thread
Results 1 to 3 of 3

Formula to search if a value is in range of 2 columns

  1. #1
    Registered User
    Join Date
    05-20-2011
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    1

    Formula to search if a value is in range of 2 columns

    Let's say I have the following:

    Column A Column B Column C

    Orange 100 200
    Black 201 300
    Yellow 301 400

    Now, I have a different worksheet that has a column of values. I want to search for each value in the ranges of Cols B and C and return the color that is in Col A.

    For example is as follows and Col A is what I am searching for and want to derive Col B:

    Column A Column B

    157 Orange
    188 Orange
    255 Black

    Is there a way I can do this?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Formula to search if a value is in range of 2 columns

    I think something like:

    =LOOKUP(A2,'Sheet1'!$B$1:$B$100,'Sheet1'!$A$1:$A$100)

    where Sheet1, A1:B100 contains the original data ... (you don't need column C) and A2 is the cell containing the first lookup value (157)

    Adjust ranges and sheetname to suit.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Formula to search if a value is in range of 2 columns

    try this macro



    Please Login or Register  to view this content.

+ 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