PDA

View Full Version : serial search



arnab0711
05-17-2012, 01:01 AM
Hi,
I have this workbook which has two sheets dump and Primary data,serials numbers are entered in dump sheet.Primary data sheet contain start and end serial no in column C & D respectively and distributor name in column E.
I want macro which will search for which range(column C & D of Primary data) each serial falls in dump sheet and also will provide the distributor name.

Bob Phillips
05-17-2012, 04:22 AM
Use this array formula

=INDEX('Primary data'!$F$2:$F$4000,MATCH(1,('Primary data'!$C$2:$C$4000<=$A2)*('Primary data'!$D$2:$D$4000>=$A2),0))

arnab0711
05-17-2012, 04:36 AM
Hi VBAX,
I used this in 'B' column of dump sheet,but its showing #NA.

Bob Phillips
05-17-2012, 05:25 AM
It is an array formula, commit with Ctrl-Shift-Enter.

arnab0711
05-17-2012, 05:41 AM
Hi VBAX,
I also wanted to know the serial number comes under which range,is there a way to find that.

Bob Phillips
05-17-2012, 07:26 AM
Yeah, this array formula should give you that

=INDEX('Primary data'!$C$2:$C$4000,MATCH(1,('Primary data'!$C$2:$C$4000<=$A2)*('Primary data'!$D$2:$D$4000>=$A2),0))&"-"&
INDEX('Primary data'!$C$2:$C$4000,MATCH(1,('Primary data'!$C$2:$C$4000<=$A2)*('Primary data'!$D$2:$D$4000>=$A2),0))