# Searching for data within ranges and columns

This is a discussion on Searching for data within ranges and columns within the Excel Questions forums, part of the Question Forums category; There are 2 sets of data: 1st set comprises columns A B C 2nd set comprises column D Columns A&amp;B ...

1. 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. 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.

3. 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. You may be able to incorporate Jaafar's MltplLookup udf:

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

#### Posting Permissions

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