vlookup with multiple rows returned?

[Danny54]

is there a way to return multiple lookup rows when searching for values from one workbook to another?

i have the following in the first workbook and its the values to be return from the next sheet base on IP
IP 10.10.10.4 is found three times (this is workbook A)

a	b	c
client	name	ip
md	car	10.10.10.1
md	boat	10.10.10.2
md	tractor	10.10.10.3
md	tractor	10.10.10.4
wd	window	192.168.1.1
wd	window	192.168.1.2
wd	window	192.168.1.3
wd	window	10.10.10.4
ggin	door	192.168.10.1
ggin	window	192.168.10.2
ggin	tile	192.168.10.3
ggin	vent	192.168.10.4

This is my functional interpretation of what I think would be the resolution of returning the system name ("A") when matching ("B") between book. (this is Workbook B)

system	ip	date	ver	client
a	10.10.10.4	1/1/2020​	1​	vlookup?
a	192.168.20.1	1/2/2020​	1​	vlookup?
b	10.10.10.4	1/3/2020​	2​	vlookup?
b	192.168.30.1	1/4/2020​	2​	vlookup?
c	192.168.30.2	1/5/2020​	3​	vlookup?
c	10.10.20.1	1/6/2020​	3​	vlookup?
d	10.10.20.2	1/7/2020​	4​	vlookup?
d	10.10.10.4	1/8/2020​	4​	vlookup?
d	10.20.31.1	1/9/2020​	4​	vlookup?

after the vlookup runs Workbook B would look like this.
it seams the vlookup function always return the first value found in the lookup. I need this and the others as well.

If someone could steer me, i would be grateful.

Thanks

a	b	c	d	e
system	ip	date	ver	value returned
a	10.10.10.4	1/1/2020​	1​	md
a	10.10.10.4	1/1/2020​	1​	wd
b	10.10.10.4	1/1/2020​	1​	md
b	10.10.10.4	1/1/2020​	1​	wd
d	10.10.10.4	1/1/2020​	1​	md
d	10.10.10.4	1/1/2020​	1​	wd
a	192.168.20.1	1/2/2020​	1​	notfound
b	192.168.30.1	1/4/2020​	2​	notfound
c	192.168.30.2	1/5/2020​	3​	notfound
c	10.10.20.1	1/6/2020​	3​	notfound
d	10.10.20.2	1/7/2020​	4​	notfound
d	10.20.31.1	1/9/2020​	4​	notfound

 

[stevod]

You may need an array that might give you the results you want, worth a try?

https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/

 

[AhoyNC]

Maybe something like this.
You will need Excel ver 2010 or later with the AGGREGATE function.
Blanks in the example below is where there wasn't a match for the IP.

Sheet 2

Book1
	A	B	C	D	E
1	system	ip	date	ver	client
2	a	10.10.10.4	1/1/2020	1	md
3	a	192.168.20.1	1/2/2020	1
4	b	10.10.10.4	1/3/2020	2	wd
5	b	192.168.30.1	1/4/2020	2
6	c	192.168.30.2	1/5/2020	3
7	c	10.10.20.1	1/6/2020	3
8	d	10.10.20.2	1/7/2020	4
9	d	10.10.10.4	1/8/2020	4
10	d	10.20.31.1	1/9/2020	4
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(INDEX(Sheet1!$A$2:$A$13,AGGREGATE(15,6,(ROW(Sheet1!$C$2:$C$13)-ROW(Sheet1!$C$2)+1)/(B2=Sheet1!$C$2:$C$13),COUNTIF($B$2:B2,B2))),"")

Sheet 1

Book1
	A	B	C
1	client	name	ip
2	md	car	10.10.10.1
3	md	boat	10.10.10.2
4	md	tractor	10.10.10.3
5	md	tractor	10.10.10.4
6	wd	window	192.168.1.1
7	wd	window	192.168.1.2
8	wd	window	192.168.1.3
9	wd	window	10.10.10.4
10	ggin	door	192.168.10.1
11	ggin	window	192.168.10.2
12	ggin	tile	192.168.10.3
13	ggin	vent	192.168.10.4
Sheet1