Searching a list for a number within a sequence...

[finman]

and reporting specific cells.

I have a spreadsheet with two lists. One list carries unique seria
numbers of items. The second list contains the manufacturing history o
the products and is setup in four columns.

The first column is the starting # of the serial number sequence
column 2 is the ending # in the sequence. Columns 3 & 4 have the Mode
and manufacture date respectively.

For Example:

Sheet 1: Item with S/N 1234567

Sheet 2: start # = 1000000, end # = 1300000, Model = M86, Mfg Date
04/01/46

What I want to do is to take the unique serial number and see if i
falls within the start # and end #. If it does. I would like to repor
the mfg. Date and model.

Can this problem be solved with formulas and named ranges…or will
need some sort of macro approach?

Any help would be greatly appreciated.

Thanks,

finma

 

[Frank Kabel]

Hi
if sheet 2 is soorted ascendingly try the following
formula for getting column C
=INDEX('sheet2'!C1:C100,MATCH(A1,'sheet2'!A1:A100,1))

 

[finman]

Kinda chunky, but I see how that can work, and then I can use a vlooku
to report my other items.

One problem I see is that if a serial number does not fall within m
indexed values I will report the last index value.

This will get me through the day...

Thanks for the help

 

[Bernie Deitrick]

finman,

You could use an array formula to find the row that the serial numbers
matches the range. With your serial number in A1 of sheet2, this array
formula (entered with ctrl-shift-enter) will return the row number or a zero
if the serial number isn't in any sequence.

=SUM((A1>=Sheet1!A1:A100)*(A1<=Sheet1!B1:B100)*ROW(Sheet1!A1:A100))

Let's say that the array formula is in B1. Then these formulas will return
the information you want:

=IF(B1<>0,INDEX(Sheet1!C:C,B1),"Not Found")
=IF(B1<>0,INDEX(Sheet1!D,B1),"Not Found")

HTH,
Bernie
MS Excel MVP

 

[finman]

Bernie,

That works!

Playing around with variations now.

Thanks for the solution and some new tools in my excel quiver

fi

 

[Bernie Deitrick]

fin,

That works!

Don't sound so surprised! ;-)

Bernie

 

[finman]

Bernie,

Not surprised, but relieved

Thanks Again

fi