Searching for data within ranges and columns

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Searching for data within ranges and columns

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    There are 2 sets of data:
    1st set comprises columns A B C
    2nd set comprises column D
    Columns A&B are min and max values in a range. Each range per row is specific in that it can not appear in another row.
    Column C is a name associated with that range(A&B).
    Column D is a set of data that needs to search against all the columns in all the rows and if it appears within any range then the Column C content will appear (IF..THEN statement)in column E

    colA,colB,colC,colD
    1,4,white,3
    5,7,pink,4
    9,11,blue,8
    12,15,orange,11

    Many thanks in anticipation.

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On the assumption that

    a) you only posted test data
    b) there are cases where a colD value would "overlap" with 3 or more of the min / xam ranges,

    then you'll need vba to stop this getting very messy.

    If the most overlaps you'll get is 2 you could do this managably with formulas.

    ...more info please.

    paddy

  3. #3
    New Member
    Join Date
    Feb 2003
    Location
    Australia
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    If I correctly guess what you want
    ColA ColB ColC ColD ColE
    Min Max Colour Value Derived
    1 4 white 3 white
    5 7 pink 4 white
    8 8 none 8 none
    9 11 blue 11 blue
    12 15 orange 12 orange

    Derived is a lookup formula like
    =VLOOKUP(D2,$A$2:$C$6,3,TRUE)

    Ed

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    You may be able to incorporate Jaafar's MltplLookup udf:

    http://www.mrexcel.com/board/viewtop...9678&forum=2&5


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com