VLookup??

D

Dave

I'm trying to populate a column based on 2 conditions. The table I want to
draw from is below.

LoopID RouteNum SeqRange
1 100 10-90
1 101 100-190
2 102 10-200

I want to assign the proper RouteNum to a list of Sequence Numbers based on
the Loop ID and what SeqRange the number falls into. The column I want to
populate is RouteNum and is shown below.

LoopID SeqNum RouteNum
1 10
1 20
1 30
..
..
2 10

Hopefully this is clear
Thanks in advance,
Dave.
 
F

Frank Kabel

Hi Dave
first divide your third column (SeqRange) in two columns with the lower
boundary in column C and the upper boundary in column D

After this use the following array formula (entered with
CTRL+sHIFT+ENTER) on your second sheet:
=MATCH('sheet1'!$B$1:$B$100,MATCH(1,(A1='sheet1'!$A$1:$A$100)*(B2>='she
et1'!$C$1:$C$100)*(B2<='sheet1'!$D$1:$D$100),0))
 
M

meng

first divide your third column (SeqRange) in two columns with the lower
boundary in column C and the upper boundary in column D

After this use the following array formula (entered with
CTRL+sHIFT+ENTER) on your second sheet:
=MATCH('sheet1'!$B$1:$B$100,MATCH(1,(A1='sheet1'!$A$1:$A$100)*(B2>='she
et1'!$C$1:$C$100)*(B2<='sheet1'!$D$1:$D$100),0))
 
Top