Array formula needed? Not sure how to query a dataset

Not sure if you should already have the host name or not. if not, then use: =IFERROR(LOOKUP(2, 1/(COUNTIF($B$6:B6,$P$7:$P$9)=0), $P$7:$P$9), LOOKUP(2, 1/(COUNTIF($B$6:B6,$U$7:$U$9)=0), $U$7:$U$9))

for the other details do a double vlookup =IFERROR(VLOOKUP(B7,$P$7:$S$9,2,0),VLOOKUP(B7,$U$7:$X$9,2,0))

Hi,

To be more exact, look at the new sheet.

Table2 needs to query table 1 and for each cell, pull in the relevant name/slot/port. This is switch 1. Then table 3 needs to do the same for switch 2.

I have manually put the values in table2 and table3 to show what needs pulling in. Each row, in table2 and table3 must pull in the data so it reads left to right and each host is on the same line, as shown in the data i inputted.

Oh, I understood it backwards. Formula in Q7 if you have the host name set up: =INDEX($G$7:$G$12,MATCH($P7&$P$5,$B$7:$B$12&$F$7:$F$12,0))

If you need to bring the hostname as well then use =INDEX($B$7:$B$12, SMALL(IF(($P$14=$F$7:$F$12), MATCH(ROW($F$7:$F$12), ROW($F$7:$F$12)), ""),ROWS($A$1:A1))), formula in P15, array entered

Please try at P7 drag across and down

=IFERROR(INDEX($B:$H,AGGREGATE(15,6,ROW($B$7:$B$12)/($F$7:$F$12=LOOKUP("z",$P$5:P$5)),ROWS(P$7:P7)),MATCH(P$6,$B$6:$H$6,)),"")

Of course it won't work. you've changed the common key that was founf in F column(switch1 or swith2) that was found in P5 and T5, the merged cells. Doing so, these cannnot be linked anymore.