Populating a table on another sheet - reference 1 cell, return another

Hello and thank you for the assistance here.

I've seen several formulas for what I want to do which look close but I can't seem to get them working.

Here's what I've got:
Sheet 1= IP Scheme
Sheet 2= Cameras

I have a master list on Sheet 1 of all devices located at a site, from that list I want to auto-populate another sheet for each device type (Camera, Radio, Access Control, ect.)

I have all of my information in tables:
IP_Scheme - the columns in question are called [IP Address] and [Device Type].

Starting on A22 in 'Cameras' I need to be able to pull in the ip address of ONLY the devices that are labeled as a "Camera" in the [Device Type] column. I want no blanks along the line. From there I can just use INDEX to pull the information I need in the other cells. My problem is the counting to make the formula move down the list and return each IP in order down the IP list that is tagged "camera"

Any help is much appreciated. I've attached an example sheet for reference.

I created a Key column in your IP Scheme table. You can hide it if you like but in L4

=[@[Device Type]]&"-"&COUNTIF($B$4:$B4, $B4)
This creates unique names for each device that you can look up.

Then for example, in Cameras! A22
=INDEX(IP_Scheme[IP Address],MATCH("Camera-"&ROWS($A$22:$A22),IP_Scheme[key],0))

Thank you Chemist, I'll give that a try.

I also found another way to do it using:

{=IFERROR(INDEX('IP Scheme'!A:A, SMALL(IF(IP_Scheme[Device Type]="Camera", ROW(IP_Scheme[Device Type])), ROWS(A$22:A22))), "")}

This pulls just fine and I can alter it to work for each device type.


I'll have to try the way you showed me too. I'm turning into a bit of a spreadsheet junkie and love this stuff >_<

** I just checked out the method you showed me and its pretty amazing as well. Thank you for the assist!