Vlookup Hlookup help

[evmancd]

So i may have over complicated this but hope someone can help me from pulling the rest of my hair out.

So i have 2 sheets one has information that is in rows and the other that i am wanting to have the formula return the value in horizontal format.

Sheet 1 has columns Switch/Port/Patch Panel/Vlan
Sheet 2 has rows (no labels) Row 1 is the patch panel number and the row below it is suppose to be for the vlan.

I am wanting to do a forumla under each cell to have it return the vlan that is listed on sheet 1 in this cell based on the patch panel number above it.

that is the best way i can describe it, can someone please help.

Thanks,

 

[lucian257]

best way that i would recomend doing it is by formatting your sheet 1 as a table. then do a vlookup as

=vlookup(a1,Table1[[patch Panel]:[vlan]],2,false)

should take care of you.

 

[evmancd]

best way that i would recomend doing it is by formatting your sheet 1 as a table. then do a vlookup as

=vlookup(a1,Table1[[patch Panel]:[vlan]],2,false)

should take care of you.

I tried that, this is my current formula and it only returns N/A

=VLOOKUP(Q2,'Switch Port Vlans'!$C:$D,2,FALSE)

I am referencing Q2 and want my data returned to Q3. The data i seek is in column D and the value of Q2 is in column C.

 

[lucian257]

I tried that, this is my current formula and it only returns N/A

=VLOOKUP(Q2,'Switch Port Vlans'!$C:$D,2,FALSE)

I am referencing Q2 and want my data returned to Q3. The data i seek is in column D and the value of Q2 is in column C.

The issue I suspect is that you are using a dollar sign. Try placing the table at a1 formatting as a table and then using the formula I gave.

 

[lucian257]

Also looking at your formula again, what order are your columns in?

 

[AhoyNC]

Is this what you are trying to do?
Copy formulas across as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	PP10	PP11	PP12	PP13	PP14	PP15	PP16	PP17	PP18
2	V1	V2	V3	V4	V5	V6	V7	V8	V9
Sheet2

Excel Workbook
	A	B	C	D
1	Switch	Port	Patch Panel	Vlan
2	S1	P1	PP10	V1
3	S2	P2	PP11	V2
4	S3	P3	PP12	V3
5	S4	P4	PP13	V4
6	S5	P5	PP14	V5
7	S6	P6	PP15	V6
8	S7	P7	PP16	V7
9	S8	P8	PP17	V8
10	S9	P9	PP18	V9
Shert1