Searching for data within ranges and columns :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Searching for data within ranges and columns
 Author Thread

pbslater
Board Regular

Joined: 25 May 2002
Posts: 11

Status: Offline

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.

Fri Feb 07, 2003 3:48 pm

PaddyD
MrExcel MVP

Joined: 02 May 2002
Posts: 5855

Status: Offline

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

Mon Feb 10, 2003 5:35 am

Mr Ed
Board Regular

Joined: 05 Feb 2003
Posts: 15
Location: Australia

Status: Offline

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

Mon Feb 10, 2003 7:41 am

PaddyD
MrExcel MVP

Joined: 02 May 2002
Posts: 5855

Status: Offline

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

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

Tue Feb 11, 2003 4:35 am
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group
 Need help posting your first question? Read how to post Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team Download VB HTML Maker to post your code on the board Check out our new index to 485 Excel Articles. Return to MrExcel Consulting All contents Copyright 1998-2004 by MrExcel.com If you believe information posted here is from your copyrighted source, notify us per the Terms of Use Excel is a registered trademark of the Microsoft Corporation.MrExcel is a registered trademark of Tickling Keys, Inc.