Need help with a table 'lookup'

[mcelotto]

I'm wondering if I can gt some help. I'm a lil stumped as to how proceed with this problem. I had a table set up (image below) what I would like to do is be able to input a column header, and then once that column header is put in (ie Wifi 1) then put in a router name (Linksys E2500) and have the "channel" value return. I've tried been able to use vlookup to pull the channel when looking up the router name, but I'm having a hard time coming up with something that will look across the multiple headers and pull the correct column. Do I have to do an IF for each one and inside that put a vlookup? Any suggestions would be appreciated to help me int he right direction. I'm also using excel 2013 (don't have anything higher, so I know that options may be limited)

Thanks
MC

 

[Max1616]

Hello!

I've made 2 assumptions when building out this formula:
1. That the Routers & your return table will always been in the same row positions (e.g. row 20 for you router)
2. That you only want to return the first row in the bottom part of your table (Row 20)

D26 =

=INDIRECT("R20C"&MATCH(D25,1:1,0),FALSE)

D27 =

=INDIRECT("R20C"&MATCH(D25,1:1,0)+1,FALSE)

The "20" in this part of the formula "R20C" needs to be changed if you want to return a router value that doesn't live in row 20.

I hope this helps!

 

[mcelotto]

Hi Max1616,
So 1 is correct the routers will in the same location. But I would like to look up in the 3 rows. I could use an IFNA for the other rows correct and modify the RC20C to R21C if I'm reading that correctly.
MC

 

[Toadstool]

Hi MCelotto,

Is this what you want?

MCelotto.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Wifi 1	CH	Wifi 2	CH	Wifi 3	CH	Wifi 4	CH	Wifi 5	CH	Wifi 6	CH	Wifi 7	CH
2
20
21		TP-Link AX1800 Wif_Fi 6	A	Linksys XX	D	X	E	Z	F	TP-Link AX1800 Wif_Fi 6	G
22		Linksys E3200	B
23		TP-Link WA99	C
24
25
26		Column Name		Wifi 5
27		Router Name		TP-Link AX1800 Wif_Fi 6
28		Channel		G
Sheet1

Cell Formulas
Range	Formula
D28	D28	=INDEX($B$2:$O$23,MATCH(D27,OFFSET($B$1,1,MATCH(D26,$B$1:$O$1,0)-1,23),0),MATCH(D26,$B$1:$O$1,0)+1)

 

[mcelotto]

@Toadstool YES that's exactly what I was looking for.. That it totally it. I'm going to "study" that formula to figure out what you did. I very much appreciate this! And @Max1616 thanks for your assistance as well. I'm sure I can use what you gave me in another spot.

 

[Toadstool]

@Toadstool YES that's exactly what I was looking for.. That it totally it. I'm going to "study" that formula to figure out what you did. I very much appreciate this! And @Max1616 thanks for your assistance as well. I'm sure I can use what you gave me in another spot.

You're welcome.

...and a quick explanation:
I'm INDEXing into the whole matrix $B$2:$O$23 so I need to give it the correct row and column.

To get the row I give MATCH an OFFSET for the search being rows 1 to 23 and to find the column I MATCH D26 across row 1.
MATCH(D27,OFFSET($B$1,1,MATCH(D26,$B$1:$O$1,0)-1,23),0),

Now I need the column so MATCH across row 1 again but add 1 to the column of the matched Wifi
MATCH(D26,$B$1:$O$1,0)+1)