Searching for data within ranges and columns

[pbslater]

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.

 

[PaddyD]

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

 

[Mr Ed]

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

 

[PaddyD]

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

http://www.mrexcel.com/board/viewtopic.php?topic=39678&forum=2&5