multiple outputs for a single entry

[jedi_jr]

I have worksheet(cover) that I enter a "value" into and I want it to lookup that value on another worksheet(data) and fill in the data from there. If that "value" appears only once on the worksheet(data) it gives me the info that I require. If it appears more than once, how do I get the info from the subsequent entrys.

 

[NBVC]

You may get what you need from this recent post.

http://www.mrexcel.com/board2/viewtopic.php?t=179906&highlight=

 

[jedi_jr]

In worksheet"cover" I have six columns:
col A - local site
col B - ipaddress
col C - facing site
col D - Link
col E - radio
col F - Device

I enter the value in Col A, and various lookups fill in the subsequent columns with the values given in worksheet"data". My issue is that the value that I enter may appear once or as many as five times in worksheet"data", and I need all of the values to be reported. For each of the entries. Please note that I cannot make changes to worksheet"data" as it is not my worksheet.

I hope that this makes my request a little clearer...

 

[NBVC]

Worksheet "Data"

Book1
	A	B	C	D	E	F
1	Local Site	IP Address	Facing Site	Link	Radio	Device
2	Development	123.456.666	1	1	2	3
3	M1	145.256.321	2	2	3	4
4	M2	555.555.666	3	3	4	5
5	J2	777.888.999	4	4	5	6
6	Development	101.125.124	5	5	6	7
7	M4	111.225.332	6	6	7	8
8	Development	888.999.111	7	7	8	9
9	F1	111.222.333	8	8	9	0
Data

Worksheet "Cover"

Book1
	A	B	C	D	E	F	G
1	Development	Local Site	IP Address	Facing Site	Link	Radio	Device
2		Development	123.456.666	1	1	2	3
3		Development	101.125.124	5	5	6	7
4		Development	888.999.111	7	7	8	9
5
6
7
8
9
Cover

A1 is where you enter you input (search item from column A in the Data sheet).

Formula in B2:
=IF(ROW(Data!$B2)-ROW(Data!$B$1)>COUNTIF(Data!$A$1:$A$9,$A$1),"",INDEX(Data!A$1:A$9,SMALL(IF(Data!$A$1:$A$9=$A$1,ROW(Data!$B$1:$B$9)),ROW(Data!$B2)-ROW(Data!$B$1))))

Adjust the ranges to suit (leave the $ symbols in tact) and confirm with CTRL+SHIFT+ENTER.

Then copy to the right as many columns as necessary and down as far as necessary.

 

[jedi_jr]

Here is a cut of my worksheet"data"

Harris MW Checklist2.xls
	A	B	C	D	E	F	G	H
3	Market	Subnet	Local	IPAddress	Facing	Link	Radio	Device
4	BC	.240	BC0755	10.56.26.3	BC0764	DS1	Constell	Netcom
5	BC	.240	BC0756	10.56.26.5	BC0761	DS1	Constell	Netcom
6	BC	.240	BC0756	10.56.26.4	BC0764	DS1	Constell	Netcom
7	BC	.240	BC0761	10.56.26.6	BC0756	DS1	Constell	Netcom
8	BC	.240	BC0764	10.56.26.2	BC0755	DS1	Constell	Netcom
9	BC	.248	BC0764	10.56.26.18	BC0756	DS1	Constell	Ethernet
10	BC	.240	BC0764	10.56.26.1	BC0756	DS1	Constell	Netcom
data

Here is my worksheet"cover" where I want the info to showup when I enter the local site name

Harris MW Checklist2.xls
	A	B	C	D	E	F
1	localsite	ipaddress	facingsite	Link	Radio	Device
2	BC0764	10.56.26.2	BC0755	DS1	Constell	Netcom
3		?	?	?	?	?
4		?	?	?	?	?
cover

in the worksheet"data" it has multiple entries for BC0764 and I can only get either the first or last one to show up on the worksheet"cover". I am sure that the formulae that I am using are not the correct formulae for this situation.

 

[NBVC]

Why don't you use the formulas that I provided. They do exactly what you are looking for?

In your B2 of Cover sheet enter this (you can copy it from here and paste it) and instead of hitting Enter, hold the Ctrl and Shift Keys down and then press Enter then copy the formula from B2 to the end of the row of data and then copy down as far as you wish. :

=IF(ROW(Data!$D2)-ROW(Data!$D$1)>COUNTIF(Data!$C$1:$C$9,$A$1),"",INDEX(Data!D$1:D$9,SMALL(IF(Data!$C$1:$C$9=$A$1,ROW(Data!$D$1:$D$9)),ROW(Data!$D2)-ROW(Data!$D$1))))

 

[jedi_jr]

Thank you NBVC, I kept getting only the first values for each entry, but I figured out what I was missing. Your formula works great.

Thanks,
Al