How To look up a value from a table with sub-column

[Redmondcys]

I understand the basic that vlookup can look for the value on the first column and return the cell next to it.

Like if on the other sheet, I manually input in separte cells as "room 1", "A", "A1", and I want it to return "value1" for me.

Please help...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]B1
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]room 1
[/TD]
[TD]value1
[/TD]
[TD]value4
[/TD]
[TD]value7
[/TD]
[TD]value10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]room 2
[/TD]
[TD]value2
[/TD]
[TD]value5
[/TD]
[TD]value8
[/TD]
[TD]value11
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]room 3
[/TD]
[TD]value3
[/TD]
[TD]value6
[/TD]
[TD]value9
[/TD]
[TD]value12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[T. Valko]

One way...

Sheet1
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][td="bgcolor: #c0c0c0"]

D​

[/td][td="bgcolor: #c0c0c0"]

E​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td][/td][td]

A​

[/td][td]

A​

[/td][td]

B​

[/td][td]

B​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td][/td][td]

A1​

[/td][td]

A2​

[/td][td]

B1​

[/td][td]

B2​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td]

room 1​

[/td][td]

value1​

[/td][td]

value4​

[/td][td]

value7​

[/td][td]

value10​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td]

room 2​

[/td][td]

value2​

[/td][td]

value5​

[/td][td]

value8​

[/td][td]

value11​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

5​

[/td][td]

room 3​

[/td][td]

value3​

[/td][td]

value6​

[/td][td]

value9​

[/td][td]

value12​

[/td][/tr]
[/table]

Sheet2
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

Room 1​

[/td][td]

A1​

[/td][td]

value1​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td]

------​

[/td][td]

------​

[/td][td]

------​

[/td][/tr]
[/table]

This formula entered in C1:

=VLOOKUP(A1,Sheet1!A2:E5,MATCH(B1,Sheet1!A2:E2,0),0)

 

[Redmondcys]

many thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[T. Valko]

You're welcome!